Lesson 5 | FOREIGN KEY constraints |
Objective | Create FOREIGN KEY constraints on database tables. |
Create FOREIGN KEY constraints on Database Tables
One of the most common uses of constraints is to enforce what is called
referential integrity[1]. Referential integrity is used to ensure that a relationship exists between a parent table and a child table.
The
FOREIGN KEY
constraint identifies a column in a table that references another column in a parent table. The referenced column in the parent table must be the primary key in the parent table, and the primary key must already be defined when the
FOREIGN KEY
constraint is created. A foreign key can include multiple columns, but there must be a matching set of columns in the primary key of the parent table. A
FOREIGN KEY
constraint can refer to the primary key of its own table. For instance, the
MANAGER_ID
of a table could be a foreign key for the
EMPLOYEE_ID
of the same table; the value entered into the
MANAGER_ID
must exist in the
EMPLOYEE_ID
for a row in the table.
A foreign key can contain a
NULL
value and not violate the foreign key constraint.
You should create an index on any foreign keys. The reason for this is explained in the
Index Foreign Keys section below.
Why index foreign keys?
It is not necessary to have an index associated with a column that is a foreign key. One foundation of relational database design is that separate, but related, data can and should be stored in separate tables. A foreign key is an excellent way to make sure that there are data values in both tables that can be used to join them together, if requested.You should not only define a foreign key to allow the join, but also create an index so that the join performs properly.
A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. A foreign key is created by defining a FOREIGN KEY constraint when you create or modify a table.
Foreign Keys may be part of a concatenated primary key
Foreign keys[2] may be part of a concatenated primary key, or they may not be part of their table's primary key at all. Consider, for example, a pair of simple DistributedNetworks customers and orders relations:
customers (customer_numb, first_name, last_name, phone)
orders (order_numb, customer_numb, order_date)
The customer number column in the orders table is a foreign key that matches the primary key of the customers table. It represents the one to-many relationship between customers and the orders they place. However, the customer number is not part of the primary key of its table but is a non-key attribute that is nonetheless a foreign key. Technically, foreign keys need not have values unless they are part of a concatenated primary key and can be null. However, in this particular database, DistributedNetworks would be in serious trouble if customer numbers were null.
There would be no way to know which customer placed an order. A relational DBMS uses the relationships indicated by matching data between primary and foreign keys. For example, assume that an DistributedNetworks employee wanted to see what titles had been ordered on order number 11102. First, the DBMS identifies the rows in the order items table that contain an order number of 11102. Then, it takes the items number from those rows and matches them to the item numbers in the items table. In the rows where there are matches, the DBMS retrieves the associated data.
Deleting the Parent Value of a Foreign Key
The value in a foreign key must match the value in the primary key of the parent tablewhen a row in the child table is inserted or modified.
If the value in the primary key of the parent table is changed, you can specify two different actions:
- The default action is to forbid the change. If a user tries to modify a value in a parent table that is referenced by rows in a child table, Oracle returns an error to the user.
- In the particular case where a referenced row in a parent table is deleted, you can also specify what is called a cascade delete. In this, deleting a referenced row in a parent table will automatically delete all the associated rows in the child table.
Because these additional deletions happen automatically, you should use this option with care.
Foreign Key Example
There are many foreign keys in the COIN
database. The LOT
table contains a foreign key that references the primary key in the AUCTION
table, and is defined with the following syntax:
CREATE TABLE LOT (
auction_id NUMBER,
lot_id NUMBER,
min_price NUMBER(11,2),
winning_bid NUMBER(11,2),
winning_bidder NUMBER,
CONSTRAINT pk_lot
PRIMARY KEY (auction_id, lot_id),
CONSTRAINT fk_auction_id
FOREIGN KEY (auction_id)
REFERENCES auction(auction_id)
);
If you want to specify when to use the Oracle delete
statement, including all associated child rows when a row in the auction table is deleted, use the following syntax.
View the code below to see it.
CREATE TABLE LOT (
auction_id NUMBER,
lot_id NUMBER,
min_price NUMBER(11,2),
winning_bid NUMBER(11,2),
winning_bidder NUMBER,
CONSTRAINT pk_lot
PRIMARY KEY (auction_id, lot_id),
CONSTRAINT fk_auction_id
FOREIGN KEY (auction_id)
REFERENCES auction(auction_id) ON DELETE CASCADE
);
Adding Foreign Keys - Exercise
Click the Exercise link below to practice adding foreign key definitions to the table creation script.
Adding Foreign Keys - Exercise
The next lesson is about
CHECK
constraints.
[1]Referential Integrity: The guarantee that a data value in one column refers to an already existing value in another column.
[2]foreign key: A field (or combination of fields) used to link tables; a corresponding primary key field occurs in the same database.