Referential integrity
Introduction
Referential integrity is the term used to describe when all the links between tables using foreign keys are present and valid. If a record in one table refers to a record in another table, and that record is actually missing for some reason, then we talk about the lack of data integrity.
An example of referential integrity
Consider these two tables. The first table is a list of Dog Owners and the second table is a list of Dogs. The relationship between the two tables is a one-to-many:
-
- Each owner can own many dogs.
- Each dog is owned by just one owner.
In the last column of the Dogs table, you can see the foreign key, ID. This foreign key is also a primary key in the Dog Owners table and the value tells you which record each dog links to in the Dog Owners table. In other words, the value tells you who the dog owner is.
Now imagine for some reason that record number 3, Mr D Lapidated, was deleted from the Dog Owners table. This may have been done by accident or may have been done deliberately. The problem that exists in the database now is that you have two records in the Dogs table (the dogs called Manic and Blip) that refer to a record in the Dog Owners table that doesn't exist. The integrity of the data has been broken and this can cause all kinds of errors in a database.
To prevent this from happening, you can often select an option when building a database to uphold all the referential integrity rules. In other words for the above example, if you did tell your database to uphold referential integrity, and you tried to delete record number 3 in the Dog Owners table, you would either not be allowed to do this or you would be given a warning message before being allowed to proceed and delete the record. If you really wanted to delete Mr D Lapidated's record from the Dog Owners table, you would normally be required to delete or modify the two Dog records for Manic and Blip first, so that no records referred to record number 3 in the Dog Owners table.