Wiki Home

Referential Integrity


Namespace: Wiki
Referential Integrity refers to the validity of the relationships between tables.
Technically a database is said to have referential integrity if it has no un-matched foreign key values.
For example, if an invoice record is deleted in the invoice table, then either all of the associated invoice detail items must be deleted or the original Invoice record must be restricted from being deleted.
Referential Integrity (RI) is dictated by business rules, not database design rules. The database must have valid relationships, however there are many ways to get to valid relationships. One can prevent the deletion of a parent when there are children. One can cascade the deletion of the children for a parent. Or one can even ignore the children when a parent is deleted. The correct approach is dictated by the requirements of the business.

RI does not limit itself to deletions. Inserting new records into a child table also needs protection of the relationship with the child and its parent, as does any editing of the parent's primary key field or fields.

The responsibility of the database designer is to ensure the integrity of the relationships in the database. The responsibility of the business person is to tell the database designer how that integrity should be protected.

Another example, deletion of a customer record. Do we cascade the deletion to all the invoices? Or do we restrict the deletion if there are any invoices? Or, even still, do we restrict the deletion for invoices in the current period or with a non-zero balance, otherwise we cascade the deletion? All of these will protect the RI, however, it is likely that only one of them will meet the business requirements.

Contributors: Nancy Folsom, Jim BoothOffsite link to http://www.jamesbooth.com
, jMM
Category Modeling Category Data Modeling
( Topic last updated: 1999.09.29 05:49:43 PM )