What does an unbounded DELETE cascade to in SQLite?

Unbounded DELETE that cascades to child tables

Warning: it works, but holds the single write lock or breaks the previous release.

What happens

A DELETE with no WHERE on a table that other tables reference with ON DELETE CASCADE empties those tables too, in the same statement, with foreign keys on, and does nothing to them with foreign keys off. The blast radius depends on a per-connection pragma, and the statement itself names only one table.

Why it is dangerous on a populated table

SQLite holds one write lock for the whole database, so anything that rebuilds a table blocks every writer for as long as the copy takes, and that time grows with the table.

Fires on

CREATE TABLE lines (id INTEGER PRIMARY KEY, order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE);
DELETE FROM orders;

The safe pattern

-- batched job, children first, explicit:
--    DELETE FROM lines WHERE order_id IN (SELECT id FROM orders WHERE status = 'closed' AND rowid BETWEEN ? AND ?);
--    DELETE FROM orders WHERE status = 'closed' AND rowid BETWEEN ? AND ?;

Fixtures

The rule ships with these files and the test suite runs them on every change: the first set must fire, the second must stay silent.

CREATE TABLE lines (id INTEGER PRIMARY KEY, order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE) STRICT;
CREATE INDEX lines_order_id ON lines (order_id);
DELETE FROM orders;

CREATE TABLE lines (id INTEGER PRIMARY KEY, order_id INTEGER REFERENCES orders(id)) STRICT;
CREATE INDEX lines_order_id ON lines (order_id);
DELETE FROM orders;

CREATE TABLE lines (id INTEGER PRIMARY KEY, order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE) STRICT;
CREATE INDEX lines_order_id ON lines (order_id);
DELETE FROM orders WHERE status = 'closed' AND rowid BETWEEN 1 AND 1000;

How to check locally

npx bolvrk check migration.sql --engine=sqlite