Referential integrity

An example of a database that has not enforced referential integrity. In this example, there is a foreign key (artist_id) value in the album table that references a non-existent artist — in other words there is a foreign key value with no corresponding primary key value in the referenced table. What happened here was that there was an artist called "Aerosmith", with an artist_id of 4, which was deleted from the artist table. However, the album "Eat the Rich" referred to this artist. With referential integrity enforced, this would not have been possible.

Referential integrity is a property of data which, when satisfied, requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute in a different (or the same) relation (table).^[1]

For referential integrity to hold in a relational database, any field in a table that is declared a foreign key can contain only values from a parent table's primary key or a candidate key. For instance, deleting a record that contains a value referred to by a foreign key in another table would break referential integrity. Some relational database management systems (RDBMS) can enforce referential integrity, normally either by deleting the foreign key rows as well to maintain integrity, or by returning an error and not performing the delete. Which method is used may be determined by a referential integrity constraint defined in a data dictionary.

Benefits of Referential Integrity

Improved data quality

An obvious benefit is the boost to the quality of data that is stored in a database. There can still be errors, but at least data references are genuine and intact.

Faster development

Referential integrity is declared. This is much more productive (one or two orders of magnitude) than writing custom programming code.

Fewer bugs

The declarations of referential integrity are more concise than the equivalent programming code. In essence, such declarations reuse the tried and tested general-purpose code in a database engine, rather than redeveloping the same logic on a case-by-case basis.

Consistency across applications

Referential integrity ensures the quality of data references across the multiple application programs that may access a database. You will note that the definitions from the Web are expressed in terms of relational databases. However, the principle of referential integrity applies more broadly. Referential integrity applies to both relational and OO databases, as well as programming languages and modeling.

References

See also

• Dangling pointer
• Declarative Referential Integrity
• Domain/key normal form
• Entity integrity
• Propagation constraint