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.

Block changes/deletion of a primary key if it would invalidate foreign keys.
Delete all records that have a corresponding foreign key.
Set to NULL all corresponding foreign keys.
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.

RESTRICT

To block any changes to a primary key or deletion of a record that contains a primary key where there is a foreign key that references it, use the RESTRICT option.

FOREIGN KEY (topic_id) REFERENCES Topic (topic_id)
                     ON UPDATE RESTRICT
                     ON DELETE RESTRICT

Cascading delete.
If you want the current record to be deleted if the corresponding record in the Topic table is deleted, you can specify CASCADE.
```
FOREIGN KEY (topic_id) REFERENCES Topic (topic_id)
                     ON UPDATE CASCADE
                     ON DELETE CASCADE
```
Warning: Cascading delete is very powerful. By mistakenly specifying this on some foreign keys, I managed to wipe out most of a database. Fortunately, I had a backup. Some systems may prompt before executing cascading deletes, but don't count on it.

SET NULL

FOREIGN KEY (topic_id) REFERENCES Topic (topic_id)
                     ON UPDATE SET NULL
                     ON DELETE SET NULL

This sets topic_id in the current table to NULL if the corresponding record in the Topic table is deleted.

Do nothing. Of course, you don't have to specify either a ON UPDATE or ON DELETE clause, in which case the DBMS won't object to foreign keys that have illegal values. It may be useful to temporarily remove this constraint when loading tables initially or making other major changes, but you generally shouldn't run a database without referential integrity checks.

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.

Enforce Referential Integrity. When this is checked, as it should be, the next two checkboxes will be enabled. If this alone is checked, you will be prevented from changing any primary key value or deleting any record with a primary key if there is a reference to it. You will be able to make changes in primary keys if the following boxes are checked.
Cascade Update Related Fields. When this is checked, changes to the primary key will cause all foreign key fields that reference it to be updated. This is a good thing to do.
Cascade Delete Related Records. This option shouldn't be chosen without some thought. If the referenced primary key record is deleted, should all records that have a foreign key with that value also be deleted? For example, in the University database, if a student was deleted from the database should all their registrations also disappear? Perhaps. If the room a section is taught in disappears, should the sections that reference be deleted, and then with another level of cascade, should all enrollments that refer to each section be deleted. Probably not -- the sections will simply be scheduled in another room.

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.