Referential Integrity in Relational Database

In this blog post, let’s at the decomposition of relations to minimize redundancy and at foreign keys to implement the links between relations. If these links are ever broken, the system will be unreliable at best and unusable at worst. Referential integrity constraints maintain and protect these links.

There is really only one referential integrity constraint: foreign keys cannot become orphans. In other words, no record in the foreign table can contain a foreign key that doesn’t match a record in the primary table. Tuples that contain foreign keys that don’t have a corresponding candidate key in the primary relation are called orphan entities. There are three ways orphan entities can be created:

A tuple is added to the foreign table with a key that does not match a candidate key in the primary table.
The candidate key in the primary table is changed.
The referenced record in the primary table is deleted.

All three of these cases must be handled if the integrity of a relationship is to be maintained. The first case, the addition of an unmatched foreign key, is usually simply prohibited. But note that unknown and nonexistent values don’t count. If the relationship is declared as optional, any number of unknown and nonexistent values can be entered without compromising referential integrity.

The second cause of orphaned entities—changing the candidate key value in the referenced table—shouldn’t occur very often. In fact, I would strongly recommend that changes to candidate keys be prohibited altogether wherever possible. (This would be an entity constraint, by the way: “Candidate keys are not allowed to change.”) But if your model does allow candidate keys to be changed, you must ensure that these changes are made in the foreign keys as well. This is known as a cascading update. Both Microsoft Jet and Microsoft SQL Server provide mechanisms for easily implementing cascading updates.

The final cause of orphan foreign keys is the deletion of the tuple containing the primary entity. If one deletes a Customer record, for example, what becomes of that customer’s orders? As with candidate key changes, you can simply prohibit the deletion of tuples in the primary relation if they are referenced in a foreign relation. This is certainly the cleanest solution if it is a reasonable restriction for your system. For when it’s not, both the Jet database engine and SQL Server provide a simple means of cascading the operation, known as a cascading delete.

But in this case, you also have a third option that’s a little harder to implement. At any rate, it can’t be implemented automatically. You might want to reassign the dependent records. This isn’t often appropriate, but it is sometimes necessary. Say, for example, that CustomerA purchases CustomerB. It might make sense to delete CustomerB and reassign all of CustomerB’s orders to CustomerA.

A special kind of referential integrity constraint is the maximum cardinality. In the data model, rules such as “Managers are allowed to have a maximum of five individuals reporting to them” are defined as referential constraints.