Indices UNIQUE parciais: unicidade sobre um subconjunto de linhas

Um indice unico parcial impoe a unicidade nao sobre a tabela inteira, mas apenas sobre as linhas que satisfazem um predicado WHERE. Ele resolve os problemas classicos que um UNIQUE comum nao consegue: um registro ativo por chave, o re-registro apos um soft delete e um unico "default" por grupo.

UNIQUE comum versus parcial

Uma restricao UNIQUE (email) proibe quaisquer dois emails iguais, ponto final. Mas sistemas reais costumam usar soft delete: a linha nao e removida, apenas marcada com deleted_at. Com um UNIQUE comum, um usuario que apagou a conta nunca consegue se cadastrar de novo com o mesmo email, porque a linha antiga continua la.

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;

Agora dois usuarios vivos com o mesmo email sao impossiveis, mas as linhas apagadas nunca entram no indice, entao um email liberado pode ser usado de novo.

Uma linha ativa por chave

O predicado e qualquer expressao booleana sobre as colunas da linha. Um caso comum: so um status "ativo" e permitido por chave, enquanto as linhas historicas se acumulam.

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';

Convivem quantos pedidos paid e cancelled voce quiser, mas um segundo draft para o mesmo user_id e rejeitado pelo banco. A regra "um carrinho por usuario" nao precisa mais viver no codigo da aplicacao, onde quebra sob concorrencia.

Um unico default por grupo

A mesma tecnica te da "exatamente um item marcado por grupo". Digamos que cada funcionario tem um departamento principal.

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

O indice ignora as linhas com is_primary = false, entao voce pode ter quantos departamentos nao principais quiser.

Pegadinhas

• Indice, nao restricao de tabela. No PostgreSQL voce nao pode declarar unicidade parcial via ADD CONSTRAINT ... UNIQUE — nao existe WHERE ali. Voce precisa de CREATE UNIQUE INDEX ... WHERE. Consequencia: uma chave estrangeira nao pode referenciar essa chave, e voce nao pode usa-la em ON CONFLICT sem nomear o mesmo predicado.
• ON CONFLICT precisa casar com o predicado. Para um upsert se prender ao indice parcial, repita a condicao: INSERT ... ON CONFLICT (email) WHERE deleted_at IS NULL DO UPDATE ....
• Um NULL na chave continua sem colidir. A unicidade compara valores, e NULL nao e igual a NULL. Se uma coluna chave aceita NULL, essas linhas nao colidem entre si — filtre-as no predicado ou mude para NULLS NOT DISTINCT (PostgreSQL 15+).
• O predicado precisa ser IMMUTABLE. Voce nao pode usar now() ou outras funcoes volateis dentro do WHERE — o planejador tem que conseguir casar as consultas com o indice.

Diferencas entre engines:

• MySQL nao tem indices parciais. O contorno usual e uma coluna gerada: email_active vale email quando a linha esta ativa e NULL caso contrario; depois voce poe um UNIQUE comum nela, contando com o fato de que NULL nao colide.
• ClickHouse nao impoe unicidade alguma: sua chave primaria so ordena os dados, e duplicatas sao permitidas. A unicidade e obtida por design (ReplacingMergeTree, agregacao na leitura), nao por um indice.

Resumo: um indice unico parcial e um UNIQUE limitado por um predicado. Recorra a ele sempre que a regra se leia como "unico entre as linhas onde X e verdadeiro".