Partial UNIQUE Indexes: Uniqueness Over a Subset of Rows

A partial unique index enforces uniqueness not over the whole table, but only over rows that satisfy a WHERE predicate. It solves the classic problems a plain UNIQUE cannot: one active record per key, re-registration after a soft delete, a single "default" per group.

Plain UNIQUE vs partial

A UNIQUE (email) constraint forbids any two matching emails, full stop. But real systems often use soft deletes: a row is not removed, just stamped with deleted_at. With a plain UNIQUE, a user who deleted their account can never sign up again with the same email, because the old row is still sitting there.

CREATE TABLE users (
  id         bigint PRIMARY KEY,
  email      text NOT NULL,
  name       text NOT NULL,
  country    text,
  deleted_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- Uniqueness only among rows that are NOT soft-deleted:
CREATE UNIQUE INDEX users_email_active_uniq
  ON users (email)
  WHERE deleted_at IS NULL;

Now two live users with the same email are impossible, but deleted rows never enter the index, so a freed email can be claimed again.

One active row per key

The predicate is any boolean expression over the row's columns. A common case: only one "active" status is allowed per key, while historical rows pile up.

CREATE TABLE orders (
  id         bigint PRIMARY KEY,
  user_id    bigint NOT NULL,
  amount     numeric(12,2) NOT NULL,
  status     text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);

-- At most one open cart (draft order) per user:
CREATE UNIQUE INDEX orders_one_draft_per_user
  ON orders (user_id)
  WHERE status = 'draft';

Any number of paid and cancelled orders coexist, but a second draft for the same user_id is rejected by the database. The "one cart per user" rule no longer needs to live in application code, where it breaks under concurrency.

A single default per group

The same technique gives you "exactly one flagged item per group". Say each employee has one primary department.

ALTER TABLE employees
  ADD COLUMN is_primary boolean NOT NULL DEFAULT false;

-- Only one primary dept row per employee name is allowed:
CREATE UNIQUE INDEX employees_one_primary
  ON employees (name)
  WHERE is_primary;

-- Second primary for the same person -> error:
INSERT INTO employees (id, name, manager_id, dept, salary, is_primary)
VALUES (10, 'Ann', NULL, 'sales', 90000, true);
INSERT INTO employees (id, name, manager_id, dept, salary, is_primary)
VALUES (11, 'Ann', NULL, 'ops', 95000, true); -- fails

Rows with is_primary = false are ignored by the index, so there can be as many non-primary departments as you like.

Gotchas

• Index, not table constraint. In PostgreSQL you cannot declare partial uniqueness via ADD CONSTRAINT ... UNIQUE — there is no WHERE there. You need CREATE UNIQUE INDEX ... WHERE. Consequence: a foreign key cannot reference such a key, and you cannot use it in ON CONFLICT without naming the same predicate.
• ON CONFLICT must match the predicate. For an upsert to latch onto the partial index, repeat the condition: INSERT ... ON CONFLICT (email) WHERE deleted_at IS NULL DO UPDATE ....
• NULL in the key still does not collide. Uniqueness compares values, and NULL is not equal to NULL. If a key column allows NULL, those rows do not conflict with each other — filter them in the predicate, or switch to NULLS NOT DISTINCT (PostgreSQL 15+).
• The predicate must be IMMUTABLE. You cannot use now() or other volatile functions inside WHERE — the planner has to be able to match queries against the index.

Engine differences:

• MySQL has no partial indexes. The usual workaround is a generated column: email_active equals email when the row is active and NULL otherwise; then you put a plain UNIQUE on it, relying on NULL not colliding.
• ClickHouse does not enforce uniqueness at all: its primary key only sorts data, and duplicates are allowed. Uniqueness is achieved by design (ReplacingMergeTree, aggregation at read time), not by an index.

Bottom line: a partial unique index is UNIQUE scoped by a predicate. Reach for it whenever the rule reads as "unique among the rows where X is true".