What Does ON DELETE CASCADE Do?

ON DELETE CASCADE says that when a parent row is deleted, every child row that references it should be deleted too. The engine does it as part of the same statement, inside the same transaction, so you never end up with orphans even for a moment.

The other referential actions are worth knowing side by side, because the difference between them is a business decision rather than a technical one. RESTRICT is the default: the delete is refused while children still point at the parent. NO ACTION behaves the same but is checked at the end of the statement rather than immediately, which matters if you delete and re-insert a parent within one transaction. SET NULL sets the child's foreign key to NULL, keeping the child but orphaning it — which only works when the column is nullable. SET DEFAULT sets the column to its declared default, which is rarely what anyone actually wants.

CASCADE is right when the child has no meaning without the parent. An order line cannot exist without its order. A book-author link cannot exist without both of them. A review of a deleted product is meaningless, so deleting it is reasonable. In all of those cases the child is a component of the parent, and keeping it would leave the database in a state the business cannot describe.

CASCADE is dangerous when the child is more valuable than the parent, or when the relationship is a reference rather than an ownership. Deleting a customer should not silently delete their order history, their invoices or their support tickets — those are records you need for accounting, for dispute resolution and for regulatory reasons. In that situation you want the delete to be refused so a human decides, or a soft delete so the record survives.

Two practical warnings. A cascade can cascade further: if a third table references the child table, deleting the original parent can remove a whole subtree, and the number of rows affected can be far larger than the statement suggests. And cascades fire the child's delete triggers, so a trigger that writes an audit row will produce one audit row per deleted child — which is usually correct, and occasionally a surprise.

Two child tables with different delete behaviours on the same parent, so the effect of each is visible in one script.

PRAGMA foreign_keys = ON;

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title VARCHAR(200) NOT NULL
);

CREATE TABLE reviews (
  id INTEGER PRIMARY KEY,
  book_id INTEGER NOT NULL REFERENCES books (id) ON DELETE CASCADE,
  rating INTEGER NOT NULL
);

CREATE TABLE book_tags (
  id INTEGER PRIMARY KEY,
  book_id INTEGER NOT NULL REFERENCES books (id) ON DELETE RESTRICT,
  tag VARCHAR(40) NOT NULL
);

INSERT INTO books (id, title) VALUES
  (1, 'A Wizard of Earthsea'),
  (2, 'Invisible Cities');

INSERT INTO reviews (id, book_id, rating) VALUES (1, 1, 5), (2, 2, 4);
INSERT INTO book_tags (id, book_id, tag) VALUES (1, 1, 'fantasy');

-- Deleting book 2 cascades to its review; no tag row blocks the delete.
DELETE FROM books WHERE id = 2;

SELECT (SELECT COUNT(*) FROM books) AS books,
       (SELECT COUNT(*) FROM reviews) AS reviews,
       (SELECT COUNT(*) FROM book_tags) AS tags;

The counts come back as one book, one review and one tag. Book 2 and its review are gone, and the CASCADE did the work — the review row was removed without a separate DELETE. Book 1, its review and its tag are untouched.

Had the script tried to delete book 1, the delete would have been refused with a foreign key violation, because book_tags has ON DELETE RESTRICT and that book still has a tag. That is the contrast the schema is built to show: CASCADE removes what depends on the parent, RESTRICT stops the parent from being deleted while something depends on it.

Two details worth calling out. The PRAGMA at the top is required for SQLite to enforce foreign keys at all — without it both the cascade and the restrict are silently ignored, and the script would report one book, two reviews and one tag, which is the sort of result that makes people think cascades do not work. And the tag table is the right place to put the RESTRICT: a curated tag is a deliberate editorial act that a human made, so it should block an accidental deletion rather than disappear with the book.