What is referential integrity, and what do ON DELETE/ON UPDATE CASCADE, SET NULL, and RESTRICT do?
Quick Answer
Referential integrity means a foreign key value always points to a row that actually exists (or is NULL, if nullable) — the database never allows a "dangling" reference. `ON DELETE`/`ON UPDATE` clauses define what happens to dependent rows when the referenced row is deleted or its key changes: `CASCADE` propagates the change/deletion, `SET NULL` nulls out the foreign key, `RESTRICT`/`NO ACTION` blocks the operation if dependents exist, and `SET DEFAULT` resets it to a default value.
Detailed Answer
CREATE TABLE departments (id SERIAL PRIMARY KEY, name VARCHAR(100));
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100),
department_id INT REFERENCES departments(id)
ON DELETE SET NULL
ON UPDATE CASCADE
);
The four referential actions
| Action | On DELETE of parent row | On UPDATE of parent key |
|---|---|---|
CASCADE | Delete dependent rows too | Update the FK value to match |
SET NULL | Set the FK column to NULL | Set the FK column to NULL |
SET DEFAULT | Set the FK column to its DEFAULT | Set the FK column to its DEFAULT |
RESTRICT / NO ACTION | Block the delete if dependents exist | Block the update if dependents exist |
(RESTRICT and NO ACTION differ subtly — NO ACTION allows the check to be deferred until the end of the transaction in engines that support deferred constraints, RESTRICT never does — but both reject the operation by default.)
Choosing the right action
CASCADEon DELETE — appropriate when the dependent rows have no meaning without the parent: deleting ablog_postshould delete itscomments.SET NULLon DELETE — appropriate when the dependent row should survive but lose the association: deleting adepartmentshouldn't delete itsemployees, just unassign them (requires the FK column to be nullable).RESTRICT/NO ACTION(the default in most engines) — the safest default for anything financially or legally significant: you generally don't want deleting acustomerto silently cascade-delete years ofordershistory. Force an explicit decision (archive first, reassign orders, etc.) rather than letting a delete cascade silently.CASCADEon UPDATE is mostly relevant only if your primary keys can ever change (uncommon with surrogate keys, more relevant if you used a natural key that could be corrected, like a mistyped SKU).
Why this matters beyond the syntax
Choosing CASCADE casually is one of the more dangerous schema decisions a team can make — a single DELETE FROM departments WHERE id = 5 can silently fan out and delete far more data than the person running it expects, with no natural "are you sure" checkpoint. Interviewers often use this question to probe whether you default to the safe option (RESTRICT, forcing deliberate handling) rather than reaching for CASCADE purely for developer convenience.