What Is Referential Integrity

Referential integrity in database management systems prevents errors

Computer database
Peter Cade / Getty Images

Referential integrity is a database feature in relational database management systems that 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.

Databases use tables to organize the information they contain. They are similar to spreadsheets, such as Excel, but vastly more capable for advanced uses.

Databases function with the use of primary keys and foreign keys, which maintain the relationship between the tables. 

Primary Key

The primary key of a database table is a unique identifier assigned to each record. Each table will have 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. However, because of privacy concerns, an assigned company ID number is a better choice to function as a primary key for employees. Some database software—such as Microsoft Access—assigns the primary key automatically, but the random key has no real meaning. It is better to use a key with meaning to the record. The simplest way to enforce referential integrity is not to allow changes to a primary key.

Foreign Key

A foreign key is an identifier in a table that matches the primary key of a different table.

The foreign key creates the relationship with a different table, and 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 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 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.

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.
  • Guaranteed 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