Inline UNIQUE or PRIMARY KEY on an added column

Warning — this works, but blocks traffic or rewrites data at scale.

What it catches

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.

Fires on

ALTER TABLE users ADD COLUMN email text UNIQUE;

Do this instead

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;

Catch this before it ships

This rule runs in the hosted service on Startup and above — add --remote with a team token, or use the GitHub Action: npx bolvrk check migration.sql

Browse all 69 rules

← BV030 BV032 →