Referential integrity refers to the accuracy and consistency of data within a relationship.

In relationships, data is linked between two or more tables. This is achieved by having the foreign key (in the associated table) reference a primary key value (in the primary – or parent – table). Because of this, we need to ensure that data on both sides of the relationship remain intact.

So, referential integrity requires that, whenever a foreign key value is used it must reference a valid, existing primary key in the parent table.

Example

For example, if we delete row number 15 in a primary table, we need to be sure that there’s no foreign key in any related table with the value of 15. We should only be able to delete a primary key if there are no associated rows. Otherwise, we would end up with an orphaned record.

So referential integrity will prevent users from:

• Adding rows to a related table if there is no associated row in the primary table.
• Changing values in a primary table that result in orphaned records in a related table.
• Deleting rows from a primary table if there are matching related rows.

Consequences of a Lack of Referential Integrity

A lack of referential integrity in a database can lead to incomplete data being returned, usually with no indication of an error. This could result in records being “lost” in the database, because they’re never returned in queries or reports.

It could also result in strange results appearing in reports (such as products without an associated company).

Or worse yet, it could result in customers not receiving products they paid for.

Worse still, it could affect life and death situations, such as a hospital patient not receiving the correct treatment, or a disaster relief team not receiving the correct supplies or information.

Data Integrity

Referential integrity is a subset of data integrity, which is concerned with the accuracy and consistency of all data (relationship or otherwise). Maintaining data integrity is a crucial part of working with databases.