Unique Constraints vs Unique Indexes: The Quiet Difference

A constraint and an index are not the same thing, even when they enforce the same rule. Most teams find this out the day they need to add a partial unique constraint and discover that constraints do not support WHERE. Or the day they want to rename a constraint and find out it has a more cumbersome syntax than renaming an index.

This is one of those Postgres details that does not matter until it does. Here is the actual difference, with code.

What each one is

A UNIQUE constraint is a logical constraint at the schema level. It is implemented under the hood by a unique index, but it is recorded in the catalog as a constraint with a name.

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL,
  CONSTRAINT users_email_unique UNIQUE (email)
);

A UNIQUE INDEX is just an index that happens to enforce uniqueness. It does not appear in the catalog as a constraint.

CREATE TABLE users (
  id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL
);
CREATE UNIQUE INDEX users_email_unique_idx ON users (email);

The physical effect is identical. The differences are administrative.

Where they actually differ

1. Foreign keys can only reference constraints. If you want another table to have REFERENCES users(email) (which is unusual but valid), you need the UNIQUE constraint, not the unique index. Foreign keys check the referenced columns against the catalog's constraint table.

If you only have a unique index, the foreign key declaration fails with "there is no unique constraint matching given keys."

2. Partial uniqueness is index-only. Constraints cannot have a WHERE clause. If you need uniqueness only on a subset of rows — "email must be unique among non-deleted users" — you have to use a unique index:

CREATE UNIQUE INDEX users_email_active_unique
  ON users (email)
  WHERE deleted_at IS NULL;

This is the reason most modern Postgres schemas reach for unique indexes by default. Soft delete patterns are common, and partial uniqueness is the right tool for them.

3. Index expressions are index-only. Constraints cannot do uniqueness on an expression — LOWER(email) for case-insensitive uniqueness, for example. Indexes can:

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

4. Adding/dropping syntax differs. Constraints use ALTER TABLE:

ALTER TABLE users ADD CONSTRAINT users_email_unique UNIQUE (email);
ALTER TABLE users DROP CONSTRAINT users_email_unique;

Indexes use CREATE and DROP:

CREATE UNIQUE INDEX users_email_unique_idx ON users (email);
DROP INDEX users_email_unique_idx;

You can also create a unique index CONCURRENTLY (no exclusive lock), then promote it to a constraint without taking another lock:

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

This is the canonical way to add a unique constraint to a large table without downtime. The USING INDEX form is genuinely useful and underused.

Practical guidance

For most tables, what I do today:

• If the column will be referenced by a foreign key from another table → UNIQUE constraint.
• If uniqueness is partial (subject to a WHERE) → unique index.
• If uniqueness is on an expression → unique index.
• For everything else → either is fine; UNIQUE constraint is slightly cleaner conceptually.

The choice matters in two cases: you need to add a foreign key later (constraint required), or you need partial/expression uniqueness (index required). For everything else it is a wash, and the team's convention should pick one and stick to it.

What I look for in a code review

• A CREATE UNIQUE INDEX on a column that will likely be referenced by future foreign keys. I usually suggest converting it to a constraint via ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX.
• A UNIQUE constraint where the team needs partial or expression uniqueness. They built it that way and then discovered the limitation.
• Long-running migrations on a busy table that drop and recreate a unique constraint instead of using CONCURRENTLY. The pattern is CREATE UNIQUE INDEX CONCURRENTLY first, then promote with USING INDEX. Less downtime.

The rule I leave behind: prefer the constraint when you can, the index when you must, and use CONCURRENTLY + USING INDEX whenever the table is large enough to care about lock duration.