Using the FOREIGN KEY to Enforce Referential Integrity

Go Up to Defining Integrity Constraints on a Table

A foreign key is a column or set of columns in one table that correspond in exact order to a column or set of columns defined as a primary key in another table. For example, in the PROJECT table, TEAM_LEADER is a foreign key referencing the primary key, EMP_NO in the EMPLOYEE table.

The PROJECT table

PROJ_ID	TEAM_LEADER	PROJ_NAME	PROJ_DESC	PRODUCT
DGPII	44	Automap	blob data	hardware
VBASE	47	Video database	blob data	software
HWRII	24	Translator upgrade	blob data	software

The EMPLOYEE table

EMP_NO	LAST_NAME	FIRST_NAME	DEPT_NO	JOB_CODE	PHONE_EXT	SALARY
24	Smith	John	100	Eng	4968	64000
48	Carter	Catherine	900	Sales	4967	72500
36	Smith	Jane	600	Admin	4800	37500

The primary reason for defining foreign keys is to ensure that data integrity is maintained when more than one table uses the same data: rows in the referencing table must always have corresponding rows in the referenced table.

InterBase enforces referential integrity in the following ways:

• The unique or primary key columns must already be defined before you can create the foreign key that references them.
• Referential integrity checks are available in the form of the ON UPDATE and ON DELETE options to the REFERENCES statement. When you create a foreign key by defining a column or table REFERENCES constraint, you can specify what should happen to the foreign key when the referenced primary key changes. The options are:

	Action specified	Effect on foreign key
	RESTRICT NO ACTION	[Default] The foreign key does not change (can cause the primary key update or delete to fail due to referential integrity checks)
	CASCADE	The corresponding foreign key is updated or deleted as appropriate to the new value of the primary key
	SET DEFAULT	Every column of the corresponding foreign key is set to its default value; fails if the default value of the foreign key is not found in the primary key
	SET NULL	Every column of the corresponding foreign key is set to NULL

• If you do not use the ON UPDATE and ON DELETE options when defining foreign keys, you must make sure that when information changes in one place, it changes in all referencing columns as well. Typically, you write triggers to do this. For example, to change a value in the EMP_NO column of the EMPLOYEE table (the primary key), that value must also be updated in the TEAM_LEADER column of the PROJECT table (the foreign key).
• If you delete a row from a table that is a primary key, you must first delete all foreign keys that reference that row. If you use the ON DELETE CASCADE option when defining the foreign keys, InterBase does this for you.

When you specify SET DEFAULT as the action, the default value used is the one in effect when the referential integrity constraint was defined. When the default for a foreign key column is changed after the referential integrity constraint is set up, the change does not have an effect on the default value used in the referential integrity constraint.

• You cannot add a value to a column defined as a foreign key unless that value exists in the referenced primary key. For example, to enter a value in the TEAM_LEADER column of the PROJECT table, that value must first exist in the EMP_NO column of the EMPLOYEE table.

The following example specifies that when a value is deleted from a primary key, the corresponding values in the foreign key are set to NULL. When the primary key is updated, the changes are cascaded so that the corresponding foreign key values match the new primary key values.

CREATE TABLE PROJECT {
. . .
TEAM LEADER INTEGER REFERENCES EMPLOYEE (EMP_NO)
ON DELETE SET NULL
ON UPDATE CASCADE
. . .};