Creating Foreign Key Constraints- Tables and Constraints

Foreign key constraints are used to ensure that a column value is contained within a defined list of values. Using a foreign key constraint is an efficient way of enforcing that data be a predefined value before an insert or update is allowed. This technique works well for the following scenarios:
• The list of values contains many entries.
• Other information about the lookup value needs to be stored.
• It is easy to select, insert, update, or delete values via SQL.

For example, suppose the EMP table is created with a DEPT_ID column. To ensure that each employee is assigned a valid department, you can create a foreign key constraint that enforces the rule that each DEPT_ID in the EMP table must exist in the DEPT table.

Tip If the condition you want to check for consists of a small list that does not change very often, consider using a check constraint instead of a foreign key constraint. For instance, if you have a column that will always be defined as containing either a 0 or a 1, a check constraint is an efficient solution.

For reference, here’s how the parent table DEPT table was created for these examples:

SQL> create table dept( dept_id number primary key, dept_desc varchar2(30));

A foreign key must reference a column in the parent table that has a primary key or a unique key defined on it. DEPT is the parent table and has a primary key defined on DEPT_ID.
You can use several methods to create a foreign key constraint. The following example creates a foreign key constraint on the DEPT_ID column in the EMP table:

SQL> create table emp( emp_id number, name varchar2(30),
dept_id constraint emp_dept_fk references dept(dept_id));

Note that the DEPT_ID data type is not explicitly defined. The foreign key constraint derives the data type from the referenced DEPT_ID column of the DEPT table. You can also explicitly specify the data type when you define a column (regardless of the foreign key definition):

SQL> create table emp( emp_id number, name varchar2(30),
dept_id number constraint emp_dept_fk references dept(dept_id));

You can also specify the foreign key definition out of line from the column definition in the CREATE TABLE statement:

SQL> create table emp( emp_id number, name varchar2(30),dept_id number, constraint emp_dept_fk foreign key (dept_id) references dept(dept_id) );

And, you can alter an existing table to add a foreign key constraint:

SQL> alter table emp add constraint emp_dept_fk foreign key (dept_id) references dept(dept_id);

Note Unlike with primary key and unique key constraints, Oracle does not automatically add an index to foreign key columns; you must explicitly create indexes on them. See Chapter 8 for a discussion on why it is important to create indexes on foreign key columns and how to detect foreign key columns that do not have associated indexes.