SQL / DEFINING TABLES AND CONSTRAINTS
Referential actions when a parent row disappears
Choose and write the right ON DELETE and ON UPDATE action on a foreign key, and predict which child rows get deleted, nulled, or blocked.
What you will learn
- Write ON DELETE CASCADE, SET NULL, SET DEFAULT, or RESTRICT on a foreign key
- Predict which child rows vanish, change, or block a parent DELETE
- Explain why SET NULL needs a nullable column and SET DEFAULT a real parent key
- Tell RESTRICT from NO ACTION by when the constraint check runs
Understanding Referential actions when a parent row disappears
A foreign key promises that every non-null child value exists in the parent. When you delete a parent row or change its key, that promise is about to break, so the database must do something before the statement finishes. A referential action is the answer you write down in advance, attached to the child's foreign key: refuse the change, delete the children along with it, or rewrite what the children point at. There is no fourth option, because a child row pointing at nothing is exactly what the constraint exists to forbid.
ON DELETE CASCADE fits children with no independent existence: an invoice line without its invoice means nothing, so it should go too. ON DELETE SET NULL fits an optional reference, since a sponsor still exists after the team folds and simply has no team, which is why the column has to be nullable. ON DELETE SET DEFAULT moves the child into a fallback bucket, and only works if that default value is itself a live parent key, otherwise the repair violates the same constraint it was supposed to satisfy. RESTRICT and NO ACTION both refuse the delete, and NO ACTION is what you get when you write nothing at all.
The difference between RESTRICT and NO ACTION is timing rather than outcome: RESTRICT rejects the delete right away, while NO ACTION waits until the end of the statement, which leaves room for a trigger or another cascade to clear the references first and allows the check to be deferred to commit time. Cascades are also transitive, so a single DELETE can walk from parent to child to grandchild and remove rows in tables you never named. Every referential action runs inside the same transaction as your DELETE, so if any step fails, the already-cascaded rows come back too.
CREATE TABLE team (
id integer PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE player (
id integer PRIMARY KEY,
team_id integer REFERENCES team(id) ON DELETE CASCADE,
name text NOT NULL
);
CREATE TABLE sponsor (
id integer PRIMARY KEY,
team_id integer REFERENCES team(id) ON DELETE SET NULL,
name text NOT NULL
);
INSERT INTO team VALUES (1, 'Harriers'), (2, 'Rovers');
INSERT INTO player VALUES (10, 1, 'Ada'), (11, 1, 'Grace'), (12, 2, 'Linus');
INSERT INTO sponsor VALUES (100, 1, 'Bolt Foods'), (101, 2, 'Kite Loans');
DELETE FROM team WHERE id = 1;
SELECT id, team_id, name FROM player ORDER BY id;
SELECT id, team_id, name FROM sponsor ORDER BY id;A referential action is the answer, declared in advance on the child's foreign key, to what happens to referencing rows when the parent row is deleted or its key changes.
Worked examples
RESTRICT refuses the delete
A child row that must survive turns the parent DELETE into an error instead of quietly changing data.
CREATE TABLE invoice (
id integer PRIMARY KEY,
number text NOT NULL
);
CREATE TABLE invoice_line (
id integer PRIMARY KEY,
invoice_id integer NOT NULL REFERENCES invoice(id) ON DELETE RESTRICT,
amount numeric(8,2) NOT NULL
);
INSERT INTO invoice VALUES (7, 'INV-0007');
INSERT INTO invoice_line VALUES (1, 7, 19.99);
DELETE FROM invoice WHERE id = 7;Example explained
Line 1The action is written on the child column, so invoice_line decides what a DELETE on invoice is allowed to do.
Line 2Nothing in invoice_line is touched: the server only checks whether key 7 is still referenced, then aborts the statement.
Line 3The message names the constraint and the referencing table, which is how you find out which child blocked you.
Line 4Dropping the ON DELETE clause entirely would behave almost the same here, because NO ACTION is the default refusal.
ON UPDATE CASCADE with a fallback default
Renaming a parent key rewrites the children, while deleting a parent key moves them to a default bucket.
CREATE TABLE category (
code text PRIMARY KEY
);
CREATE TABLE article (
id integer PRIMARY KEY,
category_code text NOT NULL DEFAULT 'general'
REFERENCES category(code)
ON UPDATE CASCADE
ON DELETE SET DEFAULT,
title text NOT NULL
);
INSERT INTO category VALUES ('general'), ('sql'), ('perl');
INSERT INTO article VALUES (1, 'sql', 'Window frames'), (2, 'perl', 'Regexp basics');
UPDATE category SET code = 'databases' WHERE code = 'sql';
DELETE FROM category WHERE code = 'perl';
SELECT id, category_code, title FROM article ORDER BY id;Example explained
Line 1ON UPDATE CASCADE rewrote article 1 from 'sql' to 'databases' the moment the parent key was renamed.
Line 2ON DELETE SET DEFAULT kept article 2 alive and put it in 'general', the value from the column's DEFAULT clause.
Line 3'general' must already exist as a category row; if it did not, the repair itself would break the foreign key and the DELETE would fail.
Line 4UPDATE 1 and DELETE 1 count only the category rows, so the two article changes never show up in the status output.
Important notes
SQLite parses referential actions but enforces nothing unless the connection runs PRAGMA foreign_keys = ON, and MySQL/InnoDB accepts the ON DELETE SET DEFAULT syntax while rejecting the constraint, so test the action rather than trusting the DDL.
ON UPDATE actions only ever fire if the parent key value can change; with surrogate integer primary keys it never does, so put your attention on the ON DELETE clause.
Common mistakes
Writing ON DELETE SET NULL on a NOT NULL column: the table is created without complaint, then every parent DELETE fails with a not-null violation, so the parent row can never be removed.
Reading DELETE 1 as proof that only one row changed, when a cascade may have silently removed hundreds of child and grandchild rows in the same statement.
Putting ON DELETE CASCADE on history tables such as audit logs, paid invoices, or shipment records, which erases exactly the data that was supposed to outlive the parent.
Try it yourself
Change, predict, then run
Create a folder table and a document table whose folder_id uses ON DELETE SET NULL, insert two folders and three documents, delete one folder, then select the documents to see which folder_id became NULL. Rebuild the same pair with ON DELETE CASCADE and compare how many documents are left.
Open the SQL workspaceCheck your understanding
A customer table has two children: an orders table with ON DELETE CASCADE and a support_ticket table with ON DELETE RESTRICT. Both have rows for customer 5. What happens when you run DELETE FROM customer WHERE id = 5?
- Nothing is deleted; the statement fails because of the support_ticket constraint.
- The orders are deleted and the customer stays, while the tickets are untouched.
- The customer and its orders are deleted, and the tickets keep pointing at a customer that no longer exists.
- The tickets are deleted first, then the customer and its orders, because CASCADE overrides RESTRICT.
Show answer
The delete and all its referential actions run in one transaction, so the refusal from support_ticket aborts the whole statement and any cascaded order deletions are rolled back with it. Option 3 is tempting because CASCADE feels like the stronger rule, but no referential action is allowed to leave a row referencing a missing parent, which is the single outcome the foreign key exists to prevent.