What do ON DELETE CASCADE, RESTRICT, SET NULL and NO ACTION mean on a foreign key?
They decide what happens to child rows when the parent row is deleted. CASCADE deletes the children, SET NULL clears the reference, RESTRICT blocks the delete immediately, and NO ACTION blocks it at the end of the statement (the default).
Explain it simply
If a college deletes a department, what should happen to its students' records? Delete them too (CASCADE)? Keep students but mark department as unknown (SET NULL)? Or refuse until students are moved elsewhere (RESTRICT)?
NO ACTION and RESTRICT both refuse. The difference is timing: RESTRICT checks at once and cannot be deferred, while NO ACTION checks at statement end and can be deferred if the constraint is declared deferrable.
Example
CREATE TABLE departments (id int PRIMARY KEY, name text);
CREATE TABLE students (
id int PRIMARY KEY,
name text,
dept_id int REFERENCES departments (id) ON DELETE SET NULL
);
CREATE TABLE marks (
id int PRIMARY KEY,
student_id int REFERENCES students (id) ON DELETE CASCADE,
score int
);
DELETE FROM students WHERE id = 5; -- also deletes that student's marks rows
DELETE FROM departments WHERE id = 1; -- students stay, dept_id becomes NULLIndex the foreign key column in the child table. PostgreSQL does not do it automatically, and without it, deleting a parent scans the whole child table.
What to say in the interview
"CASCADE removes dependants, SET NULL detaches them, RESTRICT and NO ACTION refuse the delete. I choose by business meaning, never CASCADE on important data by habit, and I always index the child foreign key column."
Common mistakes
- Forgetting to index the referencing column.