Adding Unique Constraints Using an Existing Index

A bug let two accounts register with the same email address, and the fix is to make the database refuse duplicates: ALTER TABLE accounts ADD CONSTRAINT accounts_email_key UNIQUE (email). On a 40-million-row table that statement builds a unique index while holding a lock that blocks every write, and if even one duplicate exists it fails at the end, after all that work. PostgreSQL lets you build the index first — concurrently, without blocking writes — and then promote it into a constraint with ADD CONSTRAINT ... UNIQUE USING INDEX, a metadata-only step. The same approach turns an existing index into a primary key. This guide covers deduplication, the concurrent build and its failure mode, the attach step, and the MySQL equivalent. It belongs to Adding Constraints Without Downtime.

Build the Index, Then Attach It Four steps. Find and resolve duplicates; build CREATE UNIQUE INDEX CONCURRENTLY, online; confirm the index is valid; ALTER TABLE ADD CONSTRAINT UNIQUE USING INDEX, which renames the index to the constraint and takes only a brief lock. Build the Index, Then Attach It STEP 1 Deduplicate GROUP BY … HAVING count > 1 STEP 2 Build unique index CONCURRENTLY STEP 3 Check validity indisvalid = true STEP 4 Attach constraint USING INDEX, brief lock
The expensive part — building the index — happens online; the constraint is attached in milliseconds.

Symptom / Error Signatures

The blocking form shows as sessions waiting on Lock behind ALTER TABLE accounts ADD CONSTRAINT accounts_email_key UNIQUE (email) during a migration. The duplicate problem appears as:

ERROR:  could not create unique index "accounts_email_key"
DETAIL:  Key (email)=(ana@example.com) is duplicated.

With a concurrent build, that same failure leaves an index behind that is marked INVALID — ignored by queries but still enforced for new writes, so inserts of new duplicates start failing with 23505 even though the constraint does not officially exist. And ADD CONSTRAINT ... USING INDEX itself rejects unsuitable indexes: ERROR: index "accounts_email_idx" is not valid, or cannot create a constraint using a partial index / ... expression index.

Root Cause Analysis

A unique constraint in PostgreSQL is implemented by a unique B-tree index. ADD CONSTRAINT ... UNIQUE (col) builds that index in the plain, non-concurrent way — holding a SHARE lock that blocks writes — then records the constraint. CREATE UNIQUE INDEX CONCURRENTLY builds the same index while allowing writes, at the cost of a longer build and a messier failure mode. ADD CONSTRAINT name UNIQUE USING INDEX idx then adopts the finished index as the constraint’s backing index: it renames the index to the constraint name and records the constraint, taking ACCESS EXCLUSIVE only briefly. ADD CONSTRAINT ... PRIMARY KEY USING INDEX works the same way, provided the columns are also NOT NULL.

Not every unique index can be adopted. The index must be valid, a plain B-tree, non-partial, without expressions, and with default sort ordering. Partial or expression unique indexes (for example UNIQUE (lower(email))) are perfectly good at enforcing uniqueness — they just remain indexes rather than constraints, which matters only for features that require a real constraint, such as ON CONFLICT ON CONSTRAINT or being referenced by a foreign key.

Goal Online path Resulting object
unique on plain columns CREATE UNIQUE INDEX CONCURRENTLY, then USING INDEX constraint
case-insensitive uniqueness CREATE UNIQUE INDEX CONCURRENTLY ... (lower(email)) unique index only
uniqueness among active rows CREATE UNIQUE INDEX CONCURRENTLY ... WHERE deleted_at IS NULL unique index only
new primary key unique index concurrently + NOT NULL via check, then PRIMARY KEY USING INDEX constraint
Can This Index Become a Constraint? Decision tree. If the index is not valid, drop and rebuild it. If it is valid but partial or on an expression, keep it as a unique index. If it is a plain valid unique B-tree index, attach it with ADD CONSTRAINT USING INDEX. Can This Index Become a Constraint? Is the index valid? yes no Partial or expression index? yes no Keep as unique index ADD CONSTRAINT … USING INDEX Drop it, fix duplicates, rebuild
Partial and expression indexes enforce uniqueness just as well; they simply cannot be promoted to named constraints.

Immediate Mitigation

1. Find duplicates before building anything. Run on a replica for large tables; resolve each group according to business rules (merge accounts, keep the newest, and so on).

-- PostgreSQL · read-only
SELECT email, count(*) AS copies, array_agg(id ORDER BY id) AS ids
FROM accounts
WHERE email IS NOT NULL
GROUP BY email
HAVING count(*) > 1
ORDER BY copies DESC
LIMIT 100;

2. Build the unique index concurrently.

-- PostgreSQL · must run outside a transaction · writes continue during the build
-- WARNING: if duplicates exist, the build fails at the end and leaves an INVALID index that still rejects new duplicates.
SET lock_timeout = '3s';
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS accounts_email_uidx ON accounts (email);
-- ROLLBACK PATH: DROP INDEX CONCURRENTLY IF EXISTS accounts_email_uidx;

3. If the build failed, drop the invalid index and try again. Check indisvalid, fix remaining duplicates (including any created during the build by writes the application made), and rebuild.

-- PostgreSQL · outside a transaction
SELECT indisvalid FROM pg_index WHERE indexrelid = 'accounts_email_uidx'::regclass;
DROP INDEX CONCURRENTLY IF EXISTS accounts_email_uidx;

4. Attach the index as a constraint.

-- PostgreSQL · migration role · brief ACCESS EXCLUSIVE; the index is renamed to the constraint name
BEGIN;
SET LOCAL lock_timeout = '3s';
ALTER TABLE accounts ADD CONSTRAINT accounts_email_key UNIQUE USING INDEX accounts_email_uidx;
COMMIT;
-- ROLLBACK PATH: ALTER TABLE accounts DROP CONSTRAINT accounts_email_key;  (drops the index too)

MySQL 8.0 builds unique secondary indexes online. Deduplicate first, then state the online requirement explicitly so the statement fails instead of blocking if it cannot run online:

-- MySQL 8.0 · requires ALTER · online build; replicas replay it as one statement
-- WARNING: duplicate rows make the build fail with ERROR 1062 after scanning the table.
SET SESSION lock_wait_timeout = 5;
ALTER TABLE accounts ADD UNIQUE INDEX accounts_email_key (email), ALGORITHM=INPLACE, LOCK=NONE;
-- ROLLBACK PATH: ALTER TABLE accounts DROP INDEX accounts_email_key, ALGORITHM=INPLACE, LOCK=NONE;

Permanent Fix / Long-Term Pattern

Treat a new unique constraint as a data-cleaning project followed by a two-step schema change. The application must stop creating duplicates before the index build — typically by checking in code and handling 23505 gracefully — otherwise duplicates written during the build make it fail at the last moment. Then build concurrently, verify validity, and attach. Keep deduplication idempotent so it can be rerun right before the build.

When the business rule is really “unique among active rows” or “case-insensitive”, encode that exactly with a partial or expression unique index rather than forcing a plain constraint; the index is the enforcement, and it can be built online in the same way. For the concurrent build’s general mechanics and failure handling, see cleaning up invalid indexes after a failed build; for making the build step rerunnable in a migration tool, see making index creation idempotent across retries. Replacing a primary key — for example moving from int to bigint — uses the same attach technique at its final step, covered in widening int to bigint primary keys without downtime.

Duplicates Written During the Build Timeline of a 20 minute concurrent unique index build. The application keeps inserting; at minute 12 it inserts a duplicate email because it does not yet check. The build reaches validation at minute 19 and fails because of that duplicate, leaving an invalid index. Duplicates Written During the Build duplicate inserted Index build CREATE UNIQUE INDEX CONCURRENTLY fails App inserts continuing writes 0 5 min 10 min 15 min 20 min building validation fails application traffic
A concurrent unique build can be defeated by duplicates written while it runs — fix the writer first, then build.

Verification Checklist

Frequently Asked Questions

Does ADD CONSTRAINT ... USING INDEX rebuild the index? No. It adopts the existing index, renames it to the constraint name, and records the constraint. It takes a brief ACCESS EXCLUSIVE lock but does no scanning or building.

Can I use a partial unique index as a constraint? No. USING INDEX requires a plain, non-partial, non-expression B-tree index. A partial unique index still enforces uniqueness among the rows it covers; it just remains an index rather than a named constraint.

What happens to writes that create duplicates during a concurrent unique build? The index is maintained during the build, so later phases detect the duplicate and the build fails at validation, leaving an invalid index. Make sure the application stops creating duplicates before you start.

Can a unique index replace a primary key online? Yes: build a unique index concurrently on NOT NULL columns (use the check-constraint technique for NOT NULL), then use ADD CONSTRAINT ... PRIMARY KEY USING INDEX in the same transaction that drops the old primary key.

Related Articles