DB: Relationships: Referential Integrity

Referential integrity requires every foreign key value to correspond to a primary key value. It is essential that referential integrity is maintained. The most common causes of errors are in entering key values, and in updating or deleting records containing the primary key that a foreign key refers to.

Almost all DBMSs have the ability to enforce referential integrity. SQL has statements for specifying how referential integrity should be enforced.

Options: Block changes, cascading delete, set to NULL, or nothing

When a primary key is updated or deleted, what should be done with the corresponding foreign key values? These foreign keys are now invalid, so something should be done. There are several options.

  1. Block changes/deletion of a primary key if it would invalidate foreign keys.
  2. Delete all records that have a corresponding foreign key.
  3. Set to NULL all corresponding foreign keys.
  4. Nothing. When entering data it may be expedient to turn off referential integrity checking temporarily.

These referential checking options can be specified in SQL, or in a graphical user interface (eg, in Microsoft Access).

SQL: Referential Integrity on Foreign Keys

Most databases will enforce this if a FOREIGN KEY REFERENCES constraint is specified for a foreign key. When a primary key is altered or deleted, several alternative actions can be specified.

Indicating Referential Integrity in MS Access

When a relationships are formed in the Relationships tool by dragging between the foreign key and the primary key, MS Acess asks some referential integrity questions.

Problems caused by referential integrity checking

Enter primary key records first

If you've established relationships between foreign and primary keys with either SQL or with a GUI application (eg, MS Access Relationships tool), you can not enter a record containing a foreign key for which there isn't already a primary key. This only affects the order of entry for many databases, but may be impossible in some, for example in some circular cases where a table refers directly or indirectly to itself.

The typical trick is to turn off referential constraints when tables are being loaded in bulk, for example, then reestablish the constraints once the data is loaded. Ugly.