What do ON DELETE CASCADE, SET NULL and RESTRICT do in a foreign key?
They define what happens to child rows when the parent row is deleted. CASCADE deletes the children, SET NULL clears their foreign key, and RESTRICT or NO ACTION blocks the delete.
Explain it simply
If a college closes a department, what happens to its student records? Delete them all (CASCADE), keep the students but mark department as unknown (SET NULL), or refuse to close the department while students remain (RESTRICT).
Example
CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE students (
id INT PRIMARY KEY,
name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(id)
ON DELETE SET NULL
ON UPDATE CASCADE
);| Option | Effect when parent is deleted |
|---|---|
| CASCADE | Child rows are deleted too |
| SET NULL | Child foreign key becomes NULL (column must be nullable) |
| SET DEFAULT | Child foreign key becomes its default value |
| RESTRICT | Delete is blocked immediately |
| NO ACTION | Delete is blocked at statement end (the default) |
Use CASCADE carefully. Deleting one customer that cascades through orders, payments and invoices can remove a lot of data in one command. For financial records, many teams prefer soft deletes (an is_deleted flag).
What to say in the interview
"The referential action says how the database keeps child rows valid after a parent change. CASCADE propagates, SET NULL detaches, RESTRICT or NO ACTION prevents. I choose per relationship and avoid CASCADE for audit-critical data."
Common mistakes
- Using SET NULL on a column declared NOT NULL.