What is referential integrity?

Referential integrity is a database concept that ensures the validity and consistency of relationships between tables. It prevents the creation of orphaned records or references to non-existent data, maintaining the accuracy of data linkages.

What is Referential Integrity?

In relational databases, referential integrity refers to the accuracy and consistency of data within a relationship. It ensures that a foreign key in one table always refers to a valid primary key in another table. This mechanism prevents actions that would destroy links between tables or lead to inconsistent data.

How is it enforced?

Referential integrity is primarily enforced through foreign key constraints. A foreign key is a column or a set of columns in one table that refers to the primary key in another table. When a foreign key constraint is defined, the database management system (DBMS) automatically checks that values entered into the foreign key column exist in the referenced primary key column. If an action (like an INSERT, UPDATE, or DELETE) would violate this rule, the DBMS will either reject the action or perform a pre-defined cascade action.

Key Concepts

When defining foreign key constraints, you can specify actions to take when data in the parent table (the table with the primary key) is deleted or updated. Common actions include:

• ON DELETE CASCADE: If a row in the parent table is deleted, all corresponding rows in the child table (the table with the foreign key) are also deleted.
• ON UPDATE CASCADE: If the primary key value in a parent row is updated, the corresponding foreign key values in all child rows are also updated.
• ON DELETE SET NULL: If a row in the parent table is deleted, the foreign key values in the corresponding child rows are set to NULL (assuming the foreign key column allows NULLs).
• ON UPDATE SET NULL: If the primary key value in a parent row is updated, the corresponding foreign key values in all child rows are set to NULL.
• ON DELETE RESTRICT / NO ACTION: Prevents deletion of a parent row if there are dependent child rows. This is often the default behavior.
• ON UPDATE RESTRICT / NO ACTION: Prevents an update of a primary key in a parent row if there are dependent child rows.

Example

Consider two tables: Customers and Orders. An Order must always be associated with an existing Customer. Referential integrity ensures you can't create an order for a customer who doesn't exist, and you can't delete a customer if there are still orders associated with them (unless specified by CASCADE or SET NULL actions).

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

In this example, the FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) clause enforces referential integrity. ON DELETE RESTRICT means you cannot delete a customer if they have existing orders. ON UPDATE CASCADE means if a CustomerID changes in the Customers table, it will automatically update in the Orders table.

Benefits

• Data Consistency: Ensures that relationships between tables are always valid and prevents orphaned records.
• Data Accuracy: Maintains the integrity of the database by rejecting invalid data entries or modifications.
• Prevents Errors: Reduces the likelihood of programming errors related to data relationships.
• Simplicity: Simplifies application development by offloading relationship management to the DBMS.
• Reliability: Contributes to the overall reliability and trustworthiness of the data.