What happens when PRAGMA foreign_keys is switched off and never back on?
Foreign keys switched off and not back on
Warning: it works, but holds the single write lock or breaks the previous release.
What happens
PRAGMA foreign_keys=OFF is per connection and does not reset at COMMIT. A migration that turns enforcement off for a rebuild and never turns it on leaves the runner's connection accepting orphans for as long as it lives, every later statement in the run, and any backfill sharing the connection, goes unchecked.
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;
DROP TABLE orders;The safe pattern
End the file with PRAGMA foreign_keys=ON, after the transaction that needed it off.
PRAGMA foreign_keys = OFF;
BEGIN;
DROP TABLE orders;
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.
Fires (2)
PRAGMA foreign_keys = OFF;
DROP TABLE orders;PRAGMA foreign_keys = 0;
DROP TABLE orders;Stays silent (2)
DROP TABLE orders;PRAGMA foreign_keys = OFF;
BEGIN;
DROP TABLE orders;
COMMIT;
PRAGMA foreign_keys = ON;How to check locally
SQLite support is in beta: this rule runs locally in the free CLI, static only, and not in the hosted service yet. No install, nothing leaves your machine:
npx bolvrk check migration.sql --engine=sqlite