Tuesday, August 12, 2008

What is Referential Integrity???


Referential integrity is a database concept that ensures that relationships between tables remain consistent. When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table. It also includes the techniques known as cascading update and cascading delete, which ensure that changes made to the linked table are reflected in the Primary table.


Consider the situation where we have two tables: Employees and Managers. The Employees table has a foreign key attribute entitled ManagedBy which points to the record for that employee’s manager in the Managers table. Referential integrity enforces the following three rules:

  1. We may not add a record to the Employees table unless the ManagedBy attribute points to a valid record in the Managers table.
  2. If the primary key for a record in the Managers table changes, all corresponding records in the Employees table must be modified using a cascading update.
  3. If a record in the Managers table is deleted, all corresponding records in the Employees table must be deleted using a cascading delete.