Referential Constraints

For some applications, a table might require that each row’s values for a column, or for a group of columns taken together (provided none are null), be identical to a corresponding set of columns in some other table in the database. The requirement that a matching row exist in the referenced table for each referencing row is called a referential constraint. For example, an employee number in the employee table might be within a valid range, but the employee number might no longer exist in the master employee table because the employee has resigned. Referential constraints are used for such validation of data in the database.

Consider the following example:

CREATE TABLE supplier_item (

     suppl_no       INTEGER NOT NULL PRIMARY KEY,

     item_no       INTEGER REFERENCES item (item_no),

     quantity     INTEGER,

     price        MONEY

) ;

In the above example, the REFERENCES clause in the supplier_item table definition means that values in the column supplier_item. item_no must either be NULL or be equal to some value in the column item_no in another table, item. The column item_no is called the foreign key of table supplier_item.

A foreign key is a column or combination of columns that references a primary or a candidate key of some other table. A foreign key value is either NULL or exists as a primary key value.

The table that contains a foreign key is called the referencing table and the table that contains the primary or the candidate key is called the referenced table. A referential constraint can be specified at the column level or at the table level.

During INSERT/UPDATE operations on a table containing a foreign key, a check is made to see if the foreign key value matches with a corresponding primary key value. If it does not match, the INSERT/UPDATE operation results in an error.

During INSERT/UPDATE operations on a table containing a primary/candidate key, if the values to be deleted/updated match the foreign key of the referencing table, the INSERT/UPDATE operation results in an error. Hence, a value corresponding to a primary/candidate key cannot be updated/deleted if there are references to it.

When a table containing a primary/candidate key is to be dropped, a check is made as to whether the table has any references to it. If there are tables containing foreign keys that reference the primary/candidate key of the table to be dropped, the drop operation results in an error.