Referential Integrity

Definition

Referential integrity is the rule that a foreign key value must either match an existing value in the referenced (parent) table's key, or be NULL (if permitted) — a foreign key can never point at a row that doesn't exist. It's the relational-model concept; the FOREIGN KEY constraint (5.5) is the SQL mechanism that enforces it.

How It Works

sql
CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(100));
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  department_id INT,
  FOREIGN KEY (department_id) REFERENCES departments(id)
);

If referential integrity is properly enforced, employees.department_id can only ever be a value that genuinely exists in departments.id, or NULL. Finding rows that would violate this — useful both as a data-quality audit and to understand what the constraint is actually protecting against — is a classic anti-join:

sql
SELECT e.id, e.name, e.department_id
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id
WHERE d.id IS NULL;

Any row returned here has a department_id that doesn't correspond to any real department — exactly what a real FOREIGN KEY constraint would have prevented from ever being inserted in the first place.

Edge Cases and Pitfalls

• Referential integrity is commonly violated in real systems specifically where a FOREIGN KEY constraint was never actually declared (e.g. the relationship is only "known" by convention or enforced in application code) — the anti-join query above is the standard way to discover such violations after the fact.
• Deleting or updating a referenced parent row is the main operation referential integrity restricts — this is exactly why cascading actions (5.12) exist, to specify what should happen to the child rows instead of simply failing the operation.
• Referential integrity says nothing about whether the reference is semantically sensible (e.g. an employee correctly pointing at a real department that happens to be the wrong one for their actual job) — it only guarantees the reference points at something that exists, not that it's the right something.

Key Takeaways

• Referential integrity = every foreign key value matches a real parent row, or is NULL.
• FOREIGN KEY is how SQL enforces it; an anti-join (LEFT JOIN ... WHERE parent.id IS NULL) is how you detect violations if it wasn't enforced.
• It guarantees existence of the reference, not semantic correctness of it.