Why must foreign keys be off during a SQLite table rebuild?

Table rebuild with foreign keys still enforced

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

What happens

This migration rebuilds a table the documented way, create the new table, copy, drop the old, rename, but never turns foreign keys off first. With PRAGMA foreign_keys=ON the DROP TABLE performs an implicit DELETE FROM: rows referencing the table fail the migration with a constraint error, or ON DELETE CASCADE quietly removes them.

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 orders_new (id INTEGER PRIMARY KEY, qty INTEGER NOT NULL);
INSERT INTO orders_new SELECT id, qty FROM orders;
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;

The safe pattern

PRAGMA foreign_keys = OFF;
BEGIN;
CREATE TABLE orders_new (id INTEGER PRIMARY KEY, qty INTEGER NOT NULL);
INSERT INTO orders_new SELECT id, qty FROM orders;
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;
PRAGMA foreign_key_check;
COMMIT;
PRAGMA foreign_keys = ON;

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.

BEGIN;
PRAGMA foreign_keys = OFF;
CREATE TABLE orders_new (id INTEGER PRIMARY KEY, qty INTEGER NOT NULL);
INSERT INTO orders_new SELECT id, qty FROM orders;
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;

CREATE TABLE orders_new (id INTEGER PRIMARY KEY, qty INTEGER NOT NULL);
INSERT INTO orders_new SELECT id, qty FROM orders;
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;

DROP TABLE legacy_audit;

PRAGMA foreign_keys = OFF;
BEGIN;
CREATE TABLE orders_new (id INTEGER PRIMARY KEY, qty INTEGER NOT NULL);
INSERT INTO orders_new SELECT id, qty FROM orders;
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;
PRAGMA foreign_key_check;
COMMIT;
PRAGMA foreign_keys = ON;

How to check locally

npx bolvrk check migration.sql --engine=sqlite