What is Referential Integrity?

John Spacey, updated on December 22, 2016

Referential integrity is the validity of relations in a database.

Technical Details

Referential integrity is the validity of foreign keys in a relational database. If you create a row with a foreign key, that foreign key needs to exist in the parent table as a primary key. Likewise, if you delete a row that is referred to by the foreign keys of child rows, those child rows need to be updated.

Enforcement

It is common for databases to enforce referential integrity automatically. This behavior can be defined for keys in a table definition. The following are common options.

Restrict

Don't allow parent rows to be deleted or their primary key updated. In this case, you need to update the foreign keys of child rows before deleting the parent.

Null

When a parent row is deleted or updated, set related foreign keys to null.

Default

When a parent row is deleted or updated, set related foreign keys to a default value.

Cascade

When a parent row is updated, related foreign keys are updated. When a parent row is deleted, child rows are deleted.

Complex

Databases may support complex custom rules for referential integrity such as performing different actions depending on the value of the key.

Example

An Employee table has a OFFICE_ID foreign key that refers to the primary key of the OFFICE table. The data definition instructs the database if an OFFICE is deleted to set all related Employee rows to a default that points to the head office of the firm.