When working on a schema, the question comes up: UNIQUE CONSTRAINT or UNIQUE INDEX? Both prevent duplicates, but they serve slightly different purposes.

How They Work

Using a UNIQUE CONSTRAINT:

CREATE TABLE users (
  id integer PRIMARY KEY,
  email text,
  UNIQUE (email)
);

Using a UNIQUE INDEX:

CREATE TABLE users1 (
  id INTEGER PRIMARY KEY,
  email TEXT
);

CREATE UNIQUE INDEX email_idx ON users1 (email);

Both reject duplicate inserts with the same error:

ERROR: duplicate key value violates unique constraint

The behavior is identical. That’s because under the hood, a UNIQUE CONSTRAINT is implemented as a UNIQUE INDEX. The PostgreSQL docs confirm this:

“Adding a unique constraint will automatically create a unique B-tree index on the column or group of columns listed in the constraint.”

The Difference

The practical distinction is intent and flexibility.

The clearest way to put it, from the PostgreSQL subreddit: “Constraints are modeling concepts, indexes are optimizations.”

One notable difference: constraints can be deferred, which gives flexibility in certain multi-step operations. Indexes cannot.

When to Use Which

  1. Enforce a data rule: use UNIQUE CONSTRAINT. It signals intent clearly to future maintainers.
  2. Optimize search/sort, or need conditional uniqueness (e.g., partial indexes): use UNIQUE INDEX.
  3. Don’t create both on the same column.

For most cases, UNIQUE CONSTRAINT is the right call. Reach for UNIQUE INDEX when the constraint alone isn’t flexible enough.