MySQL ON DELETE CASCADE, SET NULL and RESTRICT

A foreign key does not only refuse bad data. It can also react when a parent row is deleted or its key is changed. You choose the reaction with ON DELETE and ON UPDATE.

Action	When the parent row is deleted or updated
`RESTRICT`	The change is refused immediately
`NO ACTION`	Same as `RESTRICT` in InnoDB (the check is immediate)
`CASCADE`	The change is copied to the children: they are deleted, or their key is updated
`SET NULL`	The children’s foreign key becomes `NULL` (the column must allow `NULL`)
`SET DEFAULT`	Parsed, but not supported by InnoDB

If you write nothing, the default is RESTRICT / NO ACTION.

query.sql

SQL

CREATE TABLE projects (id INT UNSIGNED PRIMARY KEY, name VARCHAR(20) NOT NULL);

CREATE TABLE tasks (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NOT NULL,
    title VARCHAR(30) NOT NULL,
    CONSTRAINT fk_tasks_project FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE CASCADE
);

CREATE TABLE notes (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NULL,
    body VARCHAR(30) NOT NULL,
    CONSTRAINT fk_notes_project FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE SET NULL
);

CREATE TABLE invoices (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    project_id INT UNSIGNED NOT NULL,
    amount INT NOT NULL,
    CONSTRAINT fk_invoices_project FOREIGN KEY (project_id) REFERENCES projects (id) ON DELETE RESTRICT
);

INSERT INTO projects VALUES (1, 'Website'), (2, 'Mobile app');
INSERT INTO tasks (project_id, title) VALUES (1, 'Design'), (1, 'Build'), (2, 'Plan');
INSERT INTO notes (project_id, body) VALUES (1, 'Kickoff notes'), (2, 'Ideas');
INSERT INTO invoices (project_id, amount) VALUES (2, 5000);

CASCADE: delete the children too

query.sql

SQL

1
2

DELETE FROM projects WHERE id = 1;
SELECT id, project_id, title FROM tasks ORDER BY id;

Output

OUTPUT

+----+------------+-------+
| id | project_id | title |
+----+------------+-------+
|  3 |          2 | Plan  |
+----+------------+-------+
1 row in set

The two tasks of project 1 are gone, deleted by the database in the same statement.

SET NULL: keep the children, forget the parent

query.sql

SQL

SELECT id, project_id, body FROM notes ORDER BY id;

Output

OUTPUT

+----+------------+---------------+
| id | project_id | body          |
+----+------------+---------------+
|  1 |       NULL | Kickoff notes |
|  2 |          2 | Ideas         |
+----+------------+---------------+
2 rows in set

The note about project 1 is still there, with project_id = NULL.

RESTRICT: refuse

query.sql

SQL

DELETE FROM projects WHERE id = 2;

Output

OUTPUT

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`learn`.`invoices`, CONSTRAINT `fk_invoices_project` FOREIGN KEY (`project_id`) REFERENCES `projects` (`id`) ON DELETE RESTRICT)

Project 2 has an invoice, and RESTRICT protects it. To delete the project, the invoice must be dealt with first, deliberately.

Choosing the action

Relationship	Good choice
Children that are parts of the parent (order lines, comments on a post, a user’s sessions)	`ON DELETE CASCADE`
Children that can outlive the parent (posts of a deleted category)	`ON DELETE SET NULL`
Children that carry legal or financial value (invoices, orders, payments)	`ON DELETE RESTRICT`
Parent keys that never change (surrogate keys)	`ON UPDATE` rarely matters. Leave the default

The dangers of CASCADE

Silent mass deletes. Deleting one customers row with CASCADE may delete orders, order lines, payments and more. A chain of cascades can remove thousands of rows from a “small” delete.
Large cascades lock a lot and may take long.
Cascades do not fire triggers on the children (for InnoDB foreign key actions). If you audit deletes with triggers, cascaded deletes are invisible to them.
Hard to recover: the data is gone. For important data, use RESTRICT and an explicit workflow (archive, soft delete).

query.sql

SQL

CREATE TABLE audit_log (id INT AUTO_INCREMENT PRIMARY KEY, message VARCHAR(40));
CREATE TRIGGER trg_task_deleted AFTER DELETE ON tasks FOR EACH ROW INSERT INTO audit_log (message) VALUES (CONCAT('task deleted: ', OLD.title));

DELETE FROM tasks WHERE title = 'Plan';
SELECT message FROM audit_log;
INSERT INTO tasks (project_id, title) VALUES (2, 'Ship');
DELETE FROM invoices;
DELETE FROM projects WHERE id = 2;
SELECT COUNT(*) AS tasks_left, (SELECT COUNT(*) FROM audit_log) AS audit_rows FROM tasks;

Output

OUTPUT

+--------------------+
| message            |
+--------------------+
| task deleted: Plan |
+--------------------+
1 row in set

+------------+------------+
| tasks_left | audit_rows |
+------------+------------+
|          0 |          1 |
+------------+------------+
1 row in set

The trigger recorded the direct delete of “Plan”. Then a new task “Ship” was added to project 2, and project 2 was deleted. The cascade removed “Ship” (tasks_left is 0), but the audit table did not grow: it still has only the one row for “Plan”.

ON UPDATE

ON UPDATE CASCADE copies a changed parent key into the children. It matters only if parent keys change, which they should not for surrogate keys. It is useful for natural keys (a country code that is renamed).

Self-referencing keys

A table that references itself (a category tree) uses the same actions. ON DELETE CASCADE deletes a whole subtree, SET NULL turns the children into roots, and RESTRICT refuses to delete a node with children (Self-Referencing Tables).

Testing the actions

Write down what should happen, run a delete on a copy of your data, and look at what the tables contain afterwards. The behaviour of a cascading delete is easy to misjudge in your head.

dangerous

Prefer the explicit path

For important parent tables, use RESTRICT and write the “delete a customer” procedure yourself: archive, anonymise, or delete in a clear order. A cascade is a loaded convenience.