Why does SQLite refuse ADD COLUMN REFERENCES with a non-NULL default?

ADD COLUMN REFERENCES with a non-NULL default

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

What happens

When foreign keys are enforced (PRAGMA foreign_keys=ON, which most applications set on every connection), a column added with a REFERENCES clause must default to NULL, anything else fails with "Cannot add a REFERENCES column with non-NULL default value". Whether it fails depends on a per-connection pragma, so the same file passes in one runner and stops in another.

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

ALTER TABLE orders ADD COLUMN warehouse_id INTEGER REFERENCES warehouses(id) DEFAULT 1;

The safe pattern

ALTER TABLE orders ADD COLUMN warehouse_id INTEGER REFERENCES warehouses(id);
-- backfill from the application, in batches

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.

ALTER TABLE orders ADD COLUMN warehouse_id INTEGER REFERENCES warehouses(id) DEFAULT 1;

ALTER TABLE orders ADD COLUMN warehouse_id INTEGER DEFAULT 1;

ALTER TABLE orders ADD COLUMN warehouse_id INTEGER REFERENCES warehouses(id);

How to check locally

npx bolvrk check migration.sql --engine=sqlite