Managing Constraints   «Prev  Next»
Lesson 5 FOREIGN KEY constraints
ObjectiveCreate 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.

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:
  1. 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.
  2. 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.

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
    );
The next lesson is about CHECK constraints.

Adding Foreign Keys - Exercise

Click the Exercise link below to practice adding foreign key definitions to the table creation script.
Adding Foreign Keys - Exercise

[1]Referential Integrity: The guarantee that a data value in one column refers to an already existing value in another column.