DB Referential Integrity

Open

Referential integrity is maintained through the use of foreign keys. A foreign key is a column or a set of columns in one table that is used to "reference" the primary key in another table.

The rules of referential integrity ensure that these relationships between tables remain valid, and that the database does not become inconsistent. Specifically, the rules of referential integrity are as follows:

1. Insert Rule: If a table has a foreign key, every value of the foreign key must either be null or match the primary key in the other table. For example, if you are adding a record to a "Orders" table and you try to insert a Customer ID that does not exist in the "Customers" table, the insert operation would violate referential integrity and would not be allowed.

2. Delete Rule: If a row in a referenced table is deleted, then all rows with a foreign key referencing that row must be deleted (this is called cascade delete), or the delete operation must be forbidden until the foreign key is changed.

3. Update Rule: If a primary key in the referenced table is changed, then all foreign keys referencing that value must be updated to the new value (this is called cascade update), or the update operation must be forbidden until the foreign keys are changed.

Referential integrity helps prevent orphan records (records that reference non-existing entities), inaccuracies in the database, and inconsistencies between tables. In practice, these constraints are enforced by the database management system, preventing any operation that would lead to a violation of the constraints.