Enforcing Referential Integrity

Go Up to Establishing Relationships between Objects

The primary reason for defining foreign keys is to ensure that the integrity of the data is maintained when more than one table references the same data–rows in one table must always have corresponding rows in the referencing table. InterBase enforces referential integrity in the following ways:

• Before a foreign key can be added, the unique or primary keys that the foreign key references must already be defined.
• If information is changed in one place, it must be changed in every other place that it appears. InterBase does this automatically when you use the ON UPDATE option to the REFERENCES clause when defining the constraints for a table or its columns. You can specify that the foreign key value be changed to match the new primary key value (CASCADE), or that it be set to the column default (SET DEFAULT), or to null (SET NULL). If you choose NO ACTION as the ON UPDATE action, you must manually ensure that the foreign key is updated when the primary key changes.

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).

• When a row containing a primary key in one table is deleted, the meaning of any rows in another table that contain that value as a foreign key is lost unless appropriate action is taken. InterBase provides the ON DELETE option to the REFERENCES clause of CREATE TABLE and ALTER TABLE so that you can specify whether the foreign key is deleted, set to the column default, or set to null when the primary key is deleted. If you choose NO ACTION as the ON DELETE action, you must manually delete the foreign key before deleting the referenced primary key.
• InterBase also prevents users from adding a value in a column defined as a foreign key that does not reference an existing primary key value. For example, to change a value in the TEAM_LEADER column of the PROJECT table, that value must first be updated in the EMP_NO column of the EMPLOYEE table.

For more information on using PRIMARY KEY and FOREIGN KEY constraints, see Working with Tables.

Advance To:

• Normalizing the Database