Why can't SQLite ADD COLUMN with PRIMARY KEY or UNIQUE?
ADD COLUMN with PRIMARY KEY or UNIQUE
Critical: SQLite refuses the statement, so the migration fails here.
What happens
ALTER TABLE ADD COLUMN cannot carry a PRIMARY KEY or UNIQUE constraint, SQLite rejects the statement outright ("Cannot add a PRIMARY KEY column", "Cannot add a UNIQUE column"). The migration fails, and the constraint it wanted still does not exist.
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 external_id TEXT UNIQUE;The safe pattern
Add the column plain, then create the unique index as its own statement, that is what UNIQUE would have built anyway. A new primary key needs the table rebuilt: create the new table, copy, drop, rename.
ALTER TABLE orders ADD COLUMN external_id TEXT;
CREATE UNIQUE INDEX orders_external_id ON orders (external_id);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)
ALTER TABLE orders ADD COLUMN pk INTEGER PRIMARY KEY;ALTER TABLE orders ADD COLUMN external_id TEXT UNIQUE;Stays silent (1)
ALTER TABLE orders ADD COLUMN external_id TEXT;
CREATE UNIQUE INDEX orders_external_id ON orders (external_id);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