1. Home
  2. Blog
  3. Databases & SQL

Databases & SQL

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.

9 min read
ON DELETE CASCADE in SQL: CASCADE vs SET NULL vs RESTRICT

Key takeaways

  • 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

Work with Stack Quora

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.

Keep reading

More guides on testing, AI & modern development

Enjoyed this article? Pick a topic and jump straight into our newest tutorials.

Artificial Intelligence

Confusion Matrix Explained: Precision, Recall, F1 and Thresholds in Python

Confusion matrix explained with Python: TP, FP, FN, TN, precision, recall, F1, the accuracy trap, threshold choice by cost and…

Read article →
Software Development

API Pagination: Offset vs Cursor vs Keyset, Tested on 1 Million Rows

API pagination explained: offset vs keyset and cursor pagination timed on 1 million rows, duplicates when data changes, the ties…

Read article →
DevOps

Git Submodule Explained: Add, Clone, Update and Remove Safely

Git submodule explained: add, clone, update –init, update –remote, the detached HEAD trap, foreach and clean removal, tested step by…

Read article →
Test Automation

Pytest tmp path Explained: tmp_path, tmp_path_factory and Temp Files

Pytest tmp_path explained: per-test temporary folders, tmp_path_factory for shared files, monkeypatch.chdir, legacy tmpdir, retention and basetemp.

Read article →
Software Testing

Test Case Prioritization: 6 Techniques Compared With APFD

Test case prioritization compared: risk, coverage, cost, history and random orders scored with APFD and time to find all faults…

Read article →
Databases & SQL

Full Text Search in SQL: FTS5, Ranking, Phrases and Speed

Full text search in SQL with SQLite FTS5: MATCH queries, phrases, prefixes, NEAR, stemming, BM25 ranking, snippets, two gotchas and…

Read article →
Databases & SQL

Generated Columns in SQL: Virtual vs Stored, Indexes and Limits

Generated columns in SQL explained: virtual vs stored, automatic updates, write errors, storage cost, indexing, JSON fields and support in…

Read article →
Software Development

Object Freeze in JavaScript: Shallow vs Deep, Seal and const

Object freeze in JavaScript explained: freeze vs seal vs const, strict mode errors, shallow vs deep freeze, frozen arrays, Map…

Read article →
Software Development

Python hashlib: SHA-256, File Hashes, HMAC and Password Hashing

Python hashlib explained: SHA-256 for strings and files, algorithm speeds, why passwords need PBKDF2 or scrypt, safe verification and HMAC…

Read article →
Software Development

Python UUID Explained: uuid1, uuid4, uuid5, uuid7 and Database Keys

Python UUID explained: uuid4, uuid5, uuid1 privacy, time-ordered uuid7, parsing, storage formats and UUIDs as database keys, tested on Python…

Read article →
Artificial Intelligence

Random Forest in Python: Bagging, Feature Sampling and OOB From Scratch

Random forest in Python from scratch: bootstrap sampling, feature sampling, number of trees, out-of-bag evaluation and impurity vs permutation importance.

Read article →
Software Development

MIME Types Explained: Content-Type, nosniff and a Common Types List

MIME types explained and tested in a browser: Content-Type, nosniff, scripts and stylesheets with wrong types, a common MIME types…

Read article →
DevOps

Git Clean Explained: Remove Untracked Files Without Regret

Git clean explained with dry runs: -n, -f, -d, -x, -X, exclusions, paths, nested repositories and interactive mode, plus a…

Read article →
Test Automation

Pytest approx: Comparing Floats With rel, abs and Collections

Pytest approx explained: default tolerances, the near-zero trap, rel and abs, lists and dicts, NaN, NumPy, and how it differs…

Read article →
Software Testing

Accessibility Testing: WCAG Checklist, Automated Tools and Manual Checks

Accessibility testing explained: WCAG basics, an axe-core scan of a broken and a fixed page, what automation missed, contrast checks…

Read article →
Databases & SQL

SQL UNPIVOT: Turn Columns Into Rows, 4 Ways Tested

SQL UNPIVOT explained: turn columns into rows with UNION ALL, CROSS JOIN with CASE, JSON functions or native UNPIVOT, with…

Read article →
Software Development

Intl NumberFormat in JavaScript: Currency, Compact, Percent and Units

Intl.NumberFormat in JavaScript: locales, currency, compact notation, percent, units, rounding, formatToParts, the non-breaking space trap and formatter reuse.

Read article →
Databases & SQL

SQL EXPLAIN Plan: How to Read Query Plans and Fix Slow Queries

SQL EXPLAIN plan explained: read scans, index searches, covering indexes and temporary sorts, with before and after timings for five…

Read article →
Artificial Intelligence

Decision Tree in Python: Gini, Entropy and Overfitting From Scratch

Decision tree in Python from scratch: Gini impurity, entropy, information gain, best splits, readable rules, and how depth and leaf…

Read article →
DevOps

Git Blame Explained: Find Who Changed a Line, and Why

Git blame explained with a real repository: ignore whitespace, line ranges and functions, ignore-revs files for formatting commits, moved code…

Read article →
Test Automation

Pytest Benchmark: Measure Speed and Catch Performance Regressions

Pytest benchmark tutorial: the benchmark fixture, reading results, parametrized and pedantic benchmarks, saving baselines and failing the build on regressions.

Read article →
Software Testing

Defect Density: Formula, Worked Examples and How to Use It

Defect density explained: the formula, a five-module worked example, per KLOC vs per function point, severity weighting, defect removal efficiency…

Read article →
Databases & SQL

Case Insensitive Search in SQL: LOWER, LIKE, COLLATE and Indexes

Case insensitive search in SQL: LOWER, LIKE, COLLATE NOCASE and expression indexes compared with query plans, plus accented letters and…

Read article →
Databases & SQL

ON DELETE CASCADE in SQL: CASCADE vs SET NULL vs RESTRICT

ON DELETE CASCADE in SQL tested: CASCADE vs SET NULL vs RESTRICT, multi-level cascades, the SQLite foreign key pragma, row…

Read article →
Artificial Intelligence

Logistic Regression in Python: Sigmoid, Log Loss and Gradient Descent From Scratch

Logistic regression in Python from scratch: sigmoid, log loss, gradient descent, odds ratios, thresholds, sigmoid overflow and separable data, tested…

Read article →
DevOps

Git Worktree Explained: Work on Two Branches at Once

Git worktree explained: add, list, remove, prune, lock and detached worktrees, a hotfix workflow, and the errors you will hit,…

Read article →
Test Automation

Pytest ini Explained: addopts, markers, testpaths and Precedence

Pytest ini explained: testpaths, addopts, strict markers, xfail_strict, filterwarnings, minversion, config file precedence and command-line overrides, tested.

Read article →
Software Testing

Test Estimation Techniques: WBS, Three Point PERT and Monte Carlo

Test estimation techniques worked through: WBS, three point PERT, total uncertainty, a Monte Carlo check, percentage rules and Wideband Delphi.

Read article →
Artificial Intelligence

Linear Regression in Python: Least Squares From Scratch, With R Squared

Linear regression in Python from scratch: closed form, NumPy least squares and gradient descent, R squared, RMSE, outliers and curved…

Read article →
DevOps

Git Remote Explained: Add, Set-URL, Rename, Remove and Push URLs

Git remote explained: add, set-url, separate push URLs, rename, remove and prune, plus how to avoid leaking tokens in remote…

Read article →
Test Automation

Pytest Mock: mocker.patch, spy, side_effect and autospec Explained

Pytest mock explained: mocker.patch, return_value, side_effect, spy, autospec and PropertyMock, with tested examples for APIs, clocks and retries.

Read article →
Software Testing

System Testing Explained: Types, Process and a Real End-to-End Example

System testing explained: types, entry and exit criteria, the process, and a real end-to-end test suite run against a small…

Read article →
Artificial Intelligence

Naive Bayes Classifier Explained: Build a Spam Filter in Python

Naive Bayes classifier explained by building a spam filter in Python: Bayes rule, the naive assumption, Laplace smoothing, log probabilities…

Read article →
DevOps

Git Branch Explained: Create, Switch, Rename, Delete and Recover

Git branch explained with tested commands: list, create, switch, rename, delete, merged checks, recovering deleted branches, remote tracking and naming.

Read article →
Test Automation

Pytest raises: Testing Exceptions, match and excinfo

Pytest raises explained with tested examples: match regexes, excinfo, custom and chained exceptions, parametrized error cases, pytest.warns and common mistakes.

Read article →
Software Testing

Non Functional Testing: Types, Metrics and a Real Load Test

Non functional testing explained: 10 key types, measurable requirements, latency percentiles and what a small real load test revealed about…

Read article →