disallowed-unique-constraint

What triggers it

Fires on the AT_AddConstraint subcommand of ALTER TABLE when the constraint contype is CONSTR_UNIQUE and it has no indexname set — a UNIQUE constraint being built from scratch rather than attached to a pre-built index.

What does not

Non-ALTER TABLE statements and ALTER TABLE statements with no cmds are skipped, as are ADD CONSTRAINT commands whose type is not CONSTR_UNIQUE. It also skips a UNIQUE constraint that already references a pre-built index via USING INDEX, since indexname is set on that constraint.

Where it applies

Applies to every PostgreSQL version MigrationPilot targets. It works on the SQL text alone — no database connection needed.

The lock, and what it blocks

ADD CONSTRAINT UNIQUE holds ACCESS EXCLUSIVE for as long as the implicit unique index build takes, blocking every read and write on the table for the whole scan — the same cost as a non-concurrent CREATE INDEX, just less visible in the SQL.

Why it matters

ADD CONSTRAINT UNIQUE builds a unique index while holding ACCESS EXCLUSIVE. Instead, create a unique index CONCURRENTLY first, then add the constraint USING INDEX.

Unsafe, and safe

ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);

CREATE UNIQUE INDEX CONCURRENTLY uq_email ON users (email);
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE USING INDEX uq_email;

Deploy and transaction boundaries

The safe fix is two statements that cannot share a transaction: CREATE UNIQUE INDEX CONCURRENTLY, then ADD CONSTRAINT ... USING INDEX. They need to ship as separate deploy steps rather than one migration file run atomically.

What it assumes

Assumes the table holds enough rows for the scan to matter; on a small or newly created table, ADD CONSTRAINT UNIQUE is effectively instant and the two-step CONCURRENTLY workaround is unneeded overhead.

What backs this rule

Every rule is a claim about PostgreSQL, so it carries what the claim rests on: a handbook chapter that cites the manual, the incidents that put it there, and the version it was last checked against.

What the CLI prints

✗ [MP027] CRITICAL (line 1)
  Adding UNIQUE constraint "uq_email" on "users" scans the entire table under ACCESS EXCLUSIVE lock. Create the index concurrently first, then use USING INDEX.

  Safe alternative:
  -- Step 1: Create the unique index concurrently (non-blocking)
  CREATE UNIQUE INDEX CONCURRENTLY uq_email_idx ON users (...);
  
  -- Step 2: Add the constraint using the pre-built index (instant)
  ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE USING INDEX uq_email_idx;

  Why: ALTER TABLE ADD CONSTRAINT UNIQUE builds a unique index while holding ACCESS EXCLUSIVE lock, blocking all reads and writes for the entire scan. Instead, create the unique index concurrently (non-blocking), then attach it as a constraint with USING INDEX.
  Docs: https://migrationpilot.dev/rules/mp027

Generated by running the CLI's own formatter over the flagged example above, so it is the text the tool actually produces. A real run also reports the other rules that fire on the same statement; those blocks are left out here.

Turning it off

For one statement, put a comment on the line before it:

-- migrationpilot-disable MP027
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE (email);

For the whole project, in .migrationpilotrc.yml — by name or by id:

rules:
  MP027: false

# or keep it, and downgrade it
rules:
  MP027:
    severity: warning

Try it

Open this rule's flagged example in the playground. It runs in your browser — edit it and watch the finding appear and disappear.

Related rules

• MP001require-concurrent-index-creationindexes should use CONCURRENTLY
• MP030require-not-valid-checkCHECK constraints should use NOT VALID
• MP004require-lock-timeoutset lock_timeout before DDL