Referential Integrity

In order to remove redundant data in database tables, the design of the database is normalized - almost always resulting in multiple tables linked together using foreign keys. This introduces a new problem because the database engine will need to keep track of the relationship to ensure that each foreign key must actually link to a valid value in another table. This is call referential integrity.

When referential integrity is enforced, all foreign keys are properly matched with existing data in the referenced table. This simply means that if Table A contains a foreign key that references to a record in Table B, the record in Table B must exist.

For example, consider the following tables:

product table
product_id primary key
category_id foreign key

category table
category_id primary key

Let's assume that the above tables contain the following data:

0001 table 0001
0002 chair 0001
0003 fan 0002

0001 furniture
0002 electrical

What happens if the user tries to create a new record in the product table with a category_id of "0003"? There is no such record in the category table. As such, such a record in the product table will contain an invalid reference to the category table.

What happens if the user tries to delete the 1st record in the category table? Deleting the 1st record will effectively invalidate the category reference in the first 2 records in the product table.

If referential integrity is applied, both the above transactions would not be allowed by the database engine. The database engine enforces referential integrity through the use of referential constraints and triggers. Referential constraints prevent situations as describe in scenario 1 above from happening while triggers prevent situations as described in scenario 2 above from happening.