Does ADD COLUMN with a CHECK constraint scan the table in SQLite?
ADD COLUMN with a CHECK constraint scans the table
Note: this works, but it is a documented trap or a cost the author may not have meant.
What happens
Adding a column is normally a schema-only change in SQLite. With a CHECK constraint (or NOT NULL on a generated column) the whole table is scanned to verify existing rows first, under the write lock, proportional to the table's size.
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 priority INTEGER DEFAULT 0 CHECK (priority BETWEEN 0 AND 9);The safe pattern
Add the column without the CHECK when the table is large and the constraint is guaranteed by the default and the application; enforce it in a rebuild later, or accept the scan in a scheduled window.
ALTER TABLE orders ADD COLUMN priority INTEGER DEFAULT 0;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 (1)
ALTER TABLE orders ADD COLUMN priority INTEGER DEFAULT 0 CHECK (priority BETWEEN 0 AND 9);Stays silent (2)
CREATE TABLE orders (id INTEGER PRIMARY KEY);
ALTER TABLE orders ADD COLUMN priority INTEGER DEFAULT 0 CHECK (priority BETWEEN 0 AND 9);ALTER TABLE orders ADD COLUMN priority INTEGER DEFAULT 0;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