Referential integrity enforcement in SQL

Introduction

Referential integrity ensures that relationships between tables stay correct and consistent. It prevents errors like linking to data that does not exist.

When you have two tables where one depends on the other, like orders linked to customers.

When you want to avoid deleting data that other data still needs.

When you want to make sure every reference in one table matches a valid entry in another.

When you want the database to automatically update or delete related data safely.

When you want to keep your data clean and reliable without manual checks.

Syntax

SQL

CREATE TABLE ChildTable (
  id INT PRIMARY KEY,
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES ParentTable(id)
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

The FOREIGN KEY links a column in one table to a column in another table.

ON DELETE and ON UPDATE define what happens if the parent data changes or is removed.

Examples

This example prevents deleting a customer if they have orders (RESTRICT) but updates order records if the customer ID changes.

SQL

CREATE TABLE Orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
    ON DELETE RESTRICT
    ON UPDATE CASCADE
);

If a post is deleted, the post_id in Comments is set to NULL, keeping comments but removing the link.

SQL

CREATE TABLE Comments (
  comment_id INT PRIMARY KEY,
  post_id INT,
  FOREIGN KEY (post_id) REFERENCES Posts(post_id)
    ON DELETE SET NULL
);

If a course is deleted, all enrollments for that course are also deleted automatically.

SQL

CREATE TABLE Enrollment (
  student_id INT,
  course_id INT,
  PRIMARY KEY (student_id, course_id),
  FOREIGN KEY (student_id) REFERENCES Students(student_id),
  FOREIGN KEY (course_id) REFERENCES Courses(course_id)
    ON DELETE CASCADE
);

Sample Program

We create two tables: Departments and Employees. Employees link to Departments by dept_id. When a department is deleted, employees in that department have their dept_id set to NULL. After deleting department 1, employees Alice and Charlie lose their department link.

SQL

CREATE TABLE Departments (
  dept_id INT PRIMARY KEY,
  dept_name VARCHAR(50)
);

CREATE TABLE Employees (
  emp_id INT PRIMARY KEY,
  emp_name VARCHAR(50),
  dept_id INT,
  FOREIGN KEY (dept_id) REFERENCES Departments(dept_id)
    ON DELETE SET NULL
);

INSERT INTO Departments VALUES (1, 'Sales'), (2, 'HR');
INSERT INTO Employees VALUES (101, 'Alice', 1), (102, 'Bob', 2), (103, 'Charlie', 1);

DELETE FROM Departments WHERE dept_id = 1;

SELECT * FROM Employees ORDER BY emp_id;

Important Notes

Referential integrity helps avoid broken links between tables.

ON DELETE CASCADE deletes related rows automatically.

ON DELETE SET NULL keeps rows but removes the link by setting foreign key to NULL.

Summary

Referential integrity keeps data relationships correct and safe.

Foreign keys connect tables and enforce these rules.

You can control what happens when related data changes or is deleted.