PostgreSQL Unique Index vs Unique Constraint: Which to Use?

The PostgreSQL unique index vs unique constraint question comes up in almost every schema review. The two look interchangeable: both prevent duplicate values, both show up as indexes in \d output, and both work as ON CONFLICT targets for upserts. The real difference is not storage — it is the feature set wrapped around the index, plus how catalogs, ORMs, and migration tools see your schema. This guide covers how each one works, where they genuinely differ, and which to choose in production migrations.

A unique constraint is a table-level rule that forbids two rows from holding the same value — or the same combination of values — in the constrained columns. Unlike a primary key, a table can carry many unique constraints, and their columns allow NULL by default. You declare one inline in CREATE TABLE or add it later with ALTER TABLE ... ADD CONSTRAINT.

Inline and named constraint syntax

CREATE TABLE users (
  id        bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  tenant_id bigint NOT NULL,
  email     text   NOT NULL,
  CONSTRAINT users_tenant_email_key UNIQUE (tenant_id, email)
);

ALTER TABLE users
  ADD CONSTRAINT users_email_key UNIQUE (email);

A multicolumn unique constraint guarantees that the combination is unique, not each column individually — a common point of confusion.

How PostgreSQL implements it under the hood

Adding a unique constraint always creates a unique B-tree index to enforce it; there is no index-free option. Verify this in pg_indexes or with \d users — the index is typically named after the constraint. The constraint depends on that index, so PostgreSQL treats the two as one linked unit. This implementation detail drives most of the differences below.

Multi-column constraints and NULLS NOT DISTINCT

By default, PostgreSQL treats NULL values as distinct, so multiple rows with NULL in a unique column never conflict. Since PostgreSQL 15, NULLS NOT DISTINCT reverses that behavior for both constraints and unique indexes:

ALTER TABLE users
  ADD CONSTRAINT users_email_key UNIQUE NULLS NOT DISTINCT (email);

A unique index is an index object created directly with CREATE UNIQUE INDEX. It enforces uniqueness exactly like the index behind a constraint, and it also accelerates queries that filter its columns — uniqueness is enforcement layered on top of an ordinary index.

CREATE UNIQUE INDEX syntax

CREATE UNIQUE INDEX users_email_unique ON users (email);

Partial unique indexes

A partial index only covers rows matching a WHERE predicate, so uniqueness applies to a subset of the table. This is the standard PostgreSQL answer to soft delete patterns with natural keys:

-- One active user per email; soft-deleted rows are exempt
CREATE UNIQUE INDEX users_email_active_unique
  ON users (email)
  WHERE deleted_at IS NULL;

No constraint syntax can express this — ADD CONSTRAINT ... UNIQUE accepts only plain columns.

Unique indexes on expressions

When uniqueness should ignore case, trim whitespace, or apply any immutable function, build a unique index on an expression:

CREATE UNIQUE INDEX users_email_lower_unique
  ON users (lower(email));

Now [email protected] and [email protected] collide. As a bonus, the planner uses this index for queries that filter on lower(email) as well.

The storage and planner story is identical: same B-tree, same write overhead, same query plans. What differs is the feature set around the index and the metadata your tooling sees.

Catalog, tooling, and migration differences

• Catalogs: constraints live in pg_constraint; standalone indexes appear only in pg_indexes and pg_class. Schema-diff tools and many ORMs reason about constraints, so a plain unique index can be invisible to them.
• Naming: constraint-backed indexes inherit the constraint name (by default table_column_key); standalone indexes use whatever you name them.
• Dropping: DROP CONSTRAINT removes the index with it; DROP INDEX on a constraint-backed index fails outright.
• Portability: a plain UNIQUE constraint maps cleanly across MySQL, SQL Server, and SQLite; partial and expression indexes are PostgreSQL-specific extensions.

Treat the constraint as the default answer for enforcing data integrity.

Default choice for enforcing data integrity

Constraints are self-documenting: they state the rule in the schema itself, survive dumps and restores, appear in pg_constraint, and are understood by migration frameworks and database GUIs. If a plain-column rule fits, declare a constraint and move on.

Deferrable uniqueness checks in transactions

A deferrable unique constraint is checked at COMMIT instead of per row, which makes legitimate swap operations possible:

ALTER TABLE team_members
  ADD CONSTRAINT team_members_seat_key UNIQUE (team_id, seat)
  DEFERRABLE INITIALLY DEFERRED;

Swapping two seats with two UPDATE statements would raise duplicate key value violates unique constraint mid-transaction; deferred checking lets both statements land before validation runs. Unique indexes cannot be deferrable, so this pattern requires a constraint. One caveat: INSERT ... ON CONFLICT has limitations with deferrable arbiters, so check the INSERT documentation for your version before combining them.

Keeping migrations portable and self-documenting

ALTER TABLE ADD CONSTRAINT is the portable, reviewable option. Code reviewers, DBA runbooks, and cross-database tooling read it without translation — which matters when the same schema evolves across engines.

Decision flow: when to choose a unique constraint versus a unique index in PostgreSQL.

Reach for a standalone unique index when a constraint cannot express the rule, or when the build strategy matters.

Soft deletes with partial unique indexes

The partial index above is the canonical example: uniqueness applies only to rows where deleted_at IS NULL, so a soft-deleted row never blocks re-registering the same email. Upserts against a postgres partial unique index need a matching predicate:

INSERT INTO users (tenant_id, email, deleted_at)
VALUES (42, '[email protected]', NULL)
ON CONFLICT (email) WHERE deleted_at IS NULL
DO NOTHING;

For inference to find a partial index, the conflict target's WHERE must imply the index predicate — a bare ON CONFLICT (email) will not match.

Case-insensitive uniqueness with lower()

Constraints accept columns only, so any normalization — lower(), btrim(), composite expressions — requires an expression index, as shown above.

Zero-downtime builds with CONCURRENTLY

Adding a unique constraint with ALTER TABLE blocks writes while it scans the table. On a busy production system, build the index first and attach it as a constraint afterwards:

CREATE UNIQUE INDEX CONCURRENTLY users_email_key
  ON users (email);

ALTER TABLE users
  ADD CONSTRAINT users_email_key UNIQUE USING INDEX users_email_key;

CREATE INDEX CONCURRENTLY cannot run inside a transaction block, and USING INDEX accepts only a plain, non-partial, non-expression B-tree unique index — but for that common case, it turns an index into a constraint with only a brief lock.

The decision rule

Need a WHERE predicate, an expression, or a concurrent build? Use CREATE UNIQUE INDEX. Otherwise, use ADD CONSTRAINT ... UNIQUE.

Dropping a constraint drops its backing index

The constraint and its index cannot be separated:

DROP INDEX users_email_key;                         -- ERROR: other objects depend on it
ALTER TABLE users DROP CONSTRAINT users_email_key;  -- drops the index too

If you need to reshape uniqueness, build the new index first, then drop the old constraint — do not leave the table without enforcement in between unless you accept the race.

Debugging duplicate key value violates unique constraint

Error 23505 reports the index or constraint name even when no constraint exists. Look the name up to see which rule fired and whether it is partial:

SELECT conname, contype
  FROM pg_constraint
 WHERE conrelid = 'users'::regclass;

SELECT indexname, indexdef
  FROM pg_indexes
 WHERE tablename = 'users';

indexdef is your friend: it reveals the WHERE clause or expression that surprises your application.

Unique constraints on partitioned tables

On partitioned tables, every unique constraint — and every unique index — must include all columns of the partition key. PostgreSQL can only enforce global uniqueness if the check routes to the right partition, so design your partitioning scheme with unique keys in mind.