Unbounded DELETE that cascades to child tables

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

What it catches

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.

Fires on

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

Do this instead

State the predicate and batch the delete; if the intent is to clear the parent and its children, delete the children explicitly first so the migration reads the way it behaves.

-- 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 ?;

Catch this before it ships

SQLite support is in alpha: this rule runs locally in the free CLI, static only, and not in the hosted service yet: npx bolvrk check migration.sql --engine=sqlite

Browse all 104 rules

← SL026 SL028 →