ON DELETE CASCADE in SQL: CASCADE vs SET NULL vs RESTRICT
ON DELETE CASCADE tells the database to delete child rows automatically when their parent row is deleted. Learn how it compares with SET NULL, RESTRICT and NO ACTION, how a cascade travels through several tables, why SQLite ignored our foreign keys until we enabled them, and how a missing index made one delete 127 times slower.
- ON DELETE CASCADE on a foreign key deletes the child rows when the parent row is deleted, including grandchildren further down the chain.
- SQLite enforces foreign keys only after
PRAGMA foreign_keys = ON; without it, our delete left two orphan orders behind. - Deleting one customer removed 2 orders and 3 order items, but the driver’s row count still said 1.
- Deleting 200 parents with 20,000 children took 2,157 ms without an index on the child column and 17 ms with one.
Foreign keys stop you from creating orders for customers who do not exist. But what should happen when a customer is deleted and they still have orders? Leave orphans pointing at nothing, refuse the delete, or remove the orders too? ON DELETE CASCADE is the last option, and choosing between it and the alternatives is a design decision with real consequences for data safety.
All examples ran on SQLite 3.50.4 from Python. The syntax is standard and works in PostgreSQL, MySQL (InnoDB), SQL Server and Oracle; we note the differences we know of but did not run those systems.
What ON DELETE CASCADE does
A foreign key links a child table column to a parent table key. The ON DELETE clause, written on the child table, decides what happens to child rows when their parent is deleted. The options are CASCADE (delete them too), SET NULL (keep them but clear the link), SET DEFAULT, RESTRICT and NO ACTION (refuse the delete). For the basics of keys and constraints, see our SQL constraints guide.
Syntax
CREATE TABLE orders (
id INTEGER PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id) ON DELETE CASCADE,
total REAL
);
-- or as a named table constraint
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE
Our test schema
customers (id, name) -- Asha (1), Ben (2)
orders customer_id -> customers ON DELETE CASCADE
order_items order_id -> orders ON DELETE CASCADE
reviews customer_id -> customers ON DELETE SET NULL
invoices customer_id -> customers ON DELETE RESTRICT
-- Asha has orders 10 and 11 (items 100, 101, 102) and review 1000
-- Ben has order 12 (item 103) and review 1001
The SQLite pragma trap
PRAGMA foreign_keys; -- (0,) off by default
DELETE FROM customers WHERE id = 1;
-- customers 1, orders 3, order_items 4
-- orphan orders: 2
Our first run did nothing at all: SQLite parsed the foreign keys but did not enforce them, because enforcement is off by default for backward compatibility. Asha was deleted and both of the related orders stayed, pointing at a customer that no longer existed. Every new connection must run PRAGMA foreign_keys = ON. Other databases enforce foreign keys by default, though MySQL’s old MyISAM engine ignores them entirely.
CASCADE in action
PRAGMA foreign_keys = ON;
-- before: customers 2, orders 3, order_items 4, reviews 2
DELETE FROM customers WHERE id = 1;
-- after: customers 1, orders 1, order_items 1, reviews 2
With enforcement on, deleting Asha removed both related orders automatically, all in the same statement and the same transaction. If anything in the chain had failed, the whole delete would have been rolled back.
Multi-level cascades
Notice that order_items also went from 4 rows to 1. The items were not linked to customers at all; they were linked to orders, which had their own ON DELETE CASCADE. Deleting a customer deleted the orders, and deleting the orders deleted their items. Cascades follow every link with CASCADE, however deep, which is powerful and dangerous: one careless DELETE on a top-level table can empty large parts of the database. Review the whole chain before adding CASCADE to a table near the top.
SET NULL: keep the child, clear the link
SELECT * FROM reviews;
-- (1000, None, 'great') Asha's review kept, author cleared
-- (1001, 2, 'ok')
The reviews table used ON DELETE SET NULL, so Asha’s review survived with customer_id set to NULL. This suits content that has value on its own: comments, audit records, reviews and messages, often displayed as “deleted user”. The column must allow NULL; a NOT NULL column with SET NULL makes the delete fail. Queries on such tables must handle NULLs, as described in our SQL NULL values guide.
RESTRICT and NO ACTION: refuse the delete
INSERT INTO invoices VALUES (5000, 2, 20); -- Ben has an invoice
DELETE FROM customers WHERE id = 2;
-- IntegrityError: FOREIGN KEY constraint failed
-- a foreign key with no ON DELETE clause behaves like NO ACTION
DELETE FROM customers WHERE id = 1; -- logs row references Asha
-- IntegrityError: FOREIGN KEY constraint failed
Invoices are legal records that must never disappear because someone deleted a customer, so that table used RESTRICT and the delete failed. A foreign key without any ON DELETE clause defaults to NO ACTION, which also refused the delete in our test. The difference between the two is timing: RESTRICT checks immediately, while NO ACTION checks at the end of the statement (or the transaction, if the constraint is deferred), which only matters for complex multi-step changes.
ON UPDATE CASCADE
CREATE TABLE p (code TEXT PRIMARY KEY);
CREATE TABLE ch (id INT, code TEXT REFERENCES p(code) ON UPDATE CASCADE ON DELETE CASCADE);
INSERT INTO p VALUES ('IN'); INSERT INTO ch VALUES (1, 'IN');
UPDATE p SET code = 'IND';
SELECT * FROM ch; -- [(1, 'IND')]
INSERT INTO ch VALUES (2, 'XX'); -- FOREIGN KEY constraint failed
ON UPDATE CASCADE copies a changed parent key into all child rows. It is useful when keys are natural values such as country or product codes. With surrogate integer IDs that never change, you rarely need it. The last line shows the basic protection every foreign key gives: inserting a child that points to a missing parent fails.
The rowcount surprise
cur = db.execute("DELETE FROM customers WHERE id = 1")
cur.rowcount # 1 (but 2 orders and 3 items were also deleted)
The row count reported by the driver covered only the table named in the statement. The five cascaded rows were invisible. If your application logs or audits deletions by row count, it will under-report. Count the children first if you need the full number, or use triggers to log what the cascade removes, as in our SQL triggers guide.
Index the foreign key column
| Delete 200 parents (20,000 child rows) | Time |
|---|---|
| No index on the child foreign key column | 2,157 ms |
With CREATE INDEX ix ON ch(pid) |
17 ms |
For every deleted parent, the database must find the matching child rows. Without an index on the child’s foreign key column, that means scanning the whole child table, once per parent. Adding one index made the same delete 127 times faster. PostgreSQL and SQLite do not create this index automatically (MySQL InnoDB does), so add it yourself; our SQL indexes guide explains how.
Tip: the same index also speeds up the RESTRICT check and every join from parent to child, so it is almost always worth having.
Which ON DELETE action should you choose?
| Relationship | Good choice | Example |
|---|---|---|
| Child cannot exist without the parent | CASCADE | Order items of an order, sessions of a user |
| Child has value on its own | SET NULL | Comments, reviews, audit rows |
| Child must be handled first | RESTRICT / NO ACTION | Invoices, payments, legal records |
| Parent is never really deleted | Soft delete flag instead | Customers with history |
Many teams avoid deleting important parents at all and set a deleted_at column instead, keeping history intact and making mistakes reversible. Whatever you choose, test it: a cascade that removes the wrong rows is far harder to undo than one that refuses to run. Bulk removals are compared in our DELETE vs TRUNCATE vs DROP guide; note that TRUNCATE on a referenced table is refused or needs a CASCADE option in PostgreSQL.
Adding ON DELETE CASCADE to an existing table
-- PostgreSQL / MySQL / SQL Server (not run here)
ALTER TABLE orders DROP CONSTRAINT fk_orders_customer; -- MySQL: DROP FOREIGN KEY
ALTER TABLE orders ADD CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE;
Most databases cannot alter a foreign key’s action in place: drop the constraint and add it again, ideally in one transaction. SQLite cannot add or drop constraints on existing tables at all; you rebuild the table by creating a new one, copying the rows with INSERT INTO SELECT, and renaming it.
Final thoughts
ON DELETE CASCADE keeps related data consistent by removing child rows with their parent, through as many levels as you link. Use it for data that truly belongs to its parent, SET NULL for data worth keeping, and RESTRICT for records that must never vanish silently. Then cover the practical details our tests exposed: turn on foreign keys in SQLite, index every foreign key column, and do not trust the row count to tell you how much a cascade deleted.
Frequently asked questions
What does ON DELETE CASCADE do?
When a parent row is deleted, the database automatically deletes every child row that references it through that foreign key, and continues down any further cascading links.
What is the difference between CASCADE and SET NULL?
CASCADE deletes the child rows. SET NULL keeps them and sets their foreign key column to NULL, so the column must allow NULL values.
Why is ON DELETE CASCADE not working in SQLite?
SQLite does not enforce foreign keys unless you run PRAGMA foreign_keys = ON on each connection. Without it, deletes leave orphan rows behind.
What happens if I do not specify ON DELETE?
The default is NO ACTION, which blocks deleting a parent that still has child rows, so you must delete or reassign the children first.
Is ON DELETE CASCADE dangerous?
It can be. One delete on a top-level table can remove rows across many tables. Use it only where child rows have no meaning without the parent, and review the whole chain.
Keep learning
Need help with testing, WordPress or SEO?
We help teams ship better software and grow their sites. Tell us what you are working on and we will suggest a clear next step.