How Referential Integrity Ensures Database Consistency

Primary keys and foreign keys maintain referential integrity

Database on a computer
PhotoAlto/Gabriel Sanchez/Getty Images

Referential integrity is a database feature in relational database management systems. It ensures the relationships between tables in a database remain accurate by applying constraints to prevent users or applications from entering inaccurate data or pointing to data that doesn't exist.

Primary Key

The primary key of a database table is a unique identifier assigned to each record. Each table specifies one or more columns designated as the primary key. A Social Security number can be a primary key for a database listing of employees because each Social Security number is unique.

Depending on the complexity of the underlying data, it may prove useful to use compound keys as the primary key. For example, in an employee table, although a Social Security number works to identify a person, a combination of the SSN plus a hire date might isolate specific employee records if the person has joined, left, then re-joined the company.

Foreign Key

A foreign key is an identifier in a table that matches the primary key of a different table. The foreign key creates a relationship with a different table. Referential integrity refers to the relationship between these tables.

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.

Example of Referential Integrity Rules

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

  • You cannot add a record to the Employees table unless the ManagedBy attribute points to a valid record in the Managers table. Referential integrity prevents the insertion of incorrect details into a table. Any operation that doesn't satisfy the referential integrity rule fails.
  • If the primary key for a record in the Managers table changes, all corresponding records in the Employees table are modified using a cascading update.
  • If a record in the Managers table is deleted, all corresponding records in the Employees table are deleted using a cascading delete.

You probably don't want to cascade-delete employees when managers leave. Most work with databases doesn't actually delete records, but rather inactivates them. For example, personnel tables often have an "active" flag to indicate current employement. When a manager leaves, his or her "active flag" is set to null—the record isn't removed. Then, when a new manager is assigned, the employees' records are updated to reflect the new foreign-key value of the new manager.

Advantages of Referential Integrity Constraints

Using a relational database management system with referential integrity offers several advantages:

  • Prevents the entry of duplicate data
  • Prevents one table from pointing to a nonexistent field in another table
  • Guarantees consistency between "partnered" tables
  • Prevents the deletion of a record that contains a value referred to by a foreign key in another table
  • Prevents the addition of a record to a table that contains a foreign key unless there is a primary key in the linked table