Why run PRAGMA foreign_key_check after re-enabling foreign keys?

Foreign keys re-enabled without a foreign_key_check

Note: this works, but it is a documented trap or a cost the author may not have meant.

What happens

Turning foreign keys back on does not validate what happened while they were off. Step 10 of the SQLite rebuild recipe is PRAGMA foreign_key_check before COMMIT, without it, an orphaned row created during the rebuild is discovered by the first user query that joins on it.

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

PRAGMA foreign_keys = OFF;
BEGIN;
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;
PRAGMA foreign_keys = ON;

The safe pattern

PRAGMA foreign_keys = OFF;
BEGIN;
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.

PRAGMA foreign_keys = OFF;
BEGIN;
DROP TABLE orders;
ALTER TABLE orders_new RENAME TO orders;
COMMIT;
PRAGMA foreign_keys = ON;

PRAGMA foreign_keys = ON;

PRAGMA foreign_keys = OFF;
BEGIN;
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