Foreign Key Constraint

Whenever two tables contain one or more common columns, you can enforce the relationship between the tables through a referential integrity constraint with a foreign key. A foreign key requires that all column values in the child table exist in the parent table. The table that includes the foreign key is called the dependent or child table. The table that is referenced is called the parent table. An example of a foreign key constraint is when the department column of the employees table (child) must contain a department ID that exists in the departments table (parent).

Foreign keys can be made up of multiple columns. Such a composite foreign key must reference a composite primary or unique key of the exact same structure, with the same number of columns and the same datatypes. Because composite primary and unique keys are limited to 32 columns, a composite foreign key is also limited to 32

columns. You must use the same datatype for corresponding columns in the parent and child tables. The column names do not need to match. For performance purposes, you might want to add an index to the columns you define

in a child table when adding a foreign key constraint. Oracle Database XE does not do this for you automatically.

When you create a foreign key constraint on a table, you can specify the action to take when rows are deleted in the referenced (parent) table. These actions include:

■ Disallow Delete - Blocks the delete of rows from the referenced table when there are dependent rows in the table.

■ Cascade Delete - Deletes the dependent rows from the table when the corresponding parent table row is deleted from the referenced table.

■ Null on Delete - Sets the foreign key column values in the table to null values when the corresponding table row is deleted from the referenced table.