Does adding a column with an inline UNIQUE or PRIMARY KEY lock the table?
Inline UNIQUE or PRIMARY KEY on an added column
Warning: this works, but blocks traffic or rewrites data at scale.
What happens
ADD COLUMN ... UNIQUE (or PRIMARY KEY) creates the constraint's index inside the ALTER, under ACCESS EXCLUSIVE, the same trap as ADD CONSTRAINT, hidden in a column definition.
Why it is dangerous on a populated table
It completes, but the time it holds its lock or rewrites rows scales with the table, and on a busy one that is long enough for traffic to queue.
Fires on
ALTER TABLE users ADD COLUMN email text UNIQUE;The safe pattern
Add the column bare (instant), build the unique index CONCURRENTLY in its own migration, then attach it as the constraint with USING INDEX, same end state, and the index build never runs under ACCESS EXCLUSIVE.
SET lock_timeout = '5s';
ALTER TABLE users ADD COLUMN email text;
-- migration 2 (alone, outside a transaction):
-- CREATE UNIQUE INDEX CONCURRENTLY users_email_key ON users (email);
-- migration 3:
-- ALTER TABLE users ADD CONSTRAINT users_email_key
-- UNIQUE USING INDEX users_email_key;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 users ADD COLUMN email text UNIQUE;Stays silent (1)
ALTER TABLE users ADD COLUMN email text;How to check locally
This rule runs in the hosted service on Startup and above: add --remote with a team token, or use the GitHub Action. No install, nothing leaves your machine:
npx bolvrk check migration.sql --remote