Referential integrity is an integrity constraint that ensures consistency between related tables through foreign keys. It guarantees that whenever one table refers to another, the reference points to a valid, existing row and not to a missing or deleted one.

In simple terms, referential integrity prevents “broken links” between tables, such as an employee pointing to a department that does not exist.

What Referential Integrity Requires

Consider two relations:

• DEPARTMENT(dept_id, dept_name) — the referenced relation (parent).

• EMPLOYEE(emp_id, name, salary, dept_id) — the referencing relation (child), where dept_id is a foreign key.

Referential integrity enforces the rule:

Every non‑null value of the foreign key dept_id in EMPLOYEE must match some value of dept_id in DEPARTMENT.

That is:

• If an employee has a dept_id = 10, then there must be a department with dept_id = 10.

• If that department is later deleted, the DBMS must either block the deletion or handle the situation (for example, update or delete the corresponding employees) to keep the rule valid.

How Referential Integrity Is Enforced

Databases usually enforce referential integrity in two ways:

1. On Insert / Update

   • When you insert a new employee with dept_id = 15, the DBMS checks if a department with dept_id = 15 exists.

   • If not, the insert is rejected (constraint violation).

2. On Delete / Update of the Parent

   • If you delete a department that is still referenced by employees, the DBMS can:

     • Block the deletion.

     • Cascading delete: delete all employees that refer to that department.

     • Set NULL: set the foreign key to NULL (if allowed).

     • Restrict or No Action: do not allow the operation if references exist.

These rules keep the database in a logically consistent state.

Example of Referential Integrity

DEPARTMENT table:

EMPLOYEE table (with dept_id as foreign key):

Here, every dept_id in EMPLOYEE corresponds to an existing dept_id in DEPARTMENT, so referential integrity holds.

If someone tries to insert an employee with dept_id = 40 when no department with that ID exists, the DBMS rejects it under referential integrity.

Why Referential Integrity Matters?

• It ensures that relationships between tables remain valid at all times.

• It prevents orphan records and meaningless references, improving data reliability.

• It is the core reason why foreign‑key constraints exist in SQL (FOREIGN KEY ... REFERENCES ...).