Indices UNIQUE parciales: unicidad sobre un subconjunto de filas

Un indice unico parcial impone la unicidad no sobre toda la tabla, sino solo sobre las filas que cumplen un predicado WHERE. Resuelve los problemas clasicos que un UNIQUE normal no puede: un registro activo por clave, el re-registro tras un borrado logico y un unico "default" por grupo.

UNIQUE normal frente al parcial

Una restriccion UNIQUE (email) prohibe dos emails iguales cualesquiera, sin mas. Pero los sistemas reales suelen usar borrado logico: la fila no se elimina, solo se marca con deleted_at. Con un UNIQUE normal, un usuario que borro su cuenta nunca podra registrarse de nuevo con el mismo email, porque la fila antigua sigue ahi.

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;

Ahora dos usuarios vivos con el mismo email son imposibles, pero las filas borradas nunca entran en el indice, asi que un email liberado puede volver a usarse.

Una fila activa por clave

El predicado es cualquier expresion booleana sobre las columnas de la fila. Un caso habitual: solo se permite un estado "activo" por clave, mientras las filas historicas se acumulan.

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

Conviven cuantos pedidos paid y cancelled quieras, pero un segundo draft para el mismo user_id lo rechaza la base de datos. La regla "un carrito por usuario" ya no necesita vivir en el codigo de la aplicacion, donde se rompe bajo concurrencia.

Un unico default por grupo

La misma tecnica te da "exactamente un elemento marcado por grupo". Supongamos que cada empleado tiene un 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

El indice ignora las filas con is_primary = false, asi que puedes tener tantos departamentos no principales como quieras.

Trampas

• Indice, no restriccion de tabla. En PostgreSQL no puedes declarar unicidad parcial con ADD CONSTRAINT ... UNIQUE — ahi no hay WHERE. Necesitas CREATE UNIQUE INDEX ... WHERE. Consecuencia: una clave foranea no puede referenciar esa clave, y no puedes usarla en ON CONFLICT sin nombrar el mismo predicado.
• ON CONFLICT debe coincidir con el predicado. Para que un upsert se enganche al indice parcial, repite la condicion: INSERT ... ON CONFLICT (email) WHERE deleted_at IS NULL DO UPDATE ....
• Un NULL en la clave sigue sin colisionar. La unicidad compara valores, y NULL no es igual a NULL. Si una columna clave admite NULL, esas filas no chocan entre si — filtralas en el predicado o pasa a NULLS NOT DISTINCT (PostgreSQL 15+).
• El predicado debe ser IMMUTABLE. No puedes usar now() ni otras funciones volatiles dentro de WHERE — el planificador tiene que poder casar las consultas con el indice.

Diferencias entre motores:

• MySQL no tiene indices parciales. El truco habitual es una columna generada: email_active vale email cuando la fila esta activa y NULL en caso contrario; luego le pones un UNIQUE normal, apoyandote en que NULL no colisiona.
• ClickHouse no impone unicidad en absoluto: su clave primaria solo ordena los datos y se admiten duplicados. La unicidad se logra por diseno (ReplacingMergeTree, agregacion en lectura), no con un indice.

En resumen: un indice unico parcial es un UNIQUE acotado por un predicado. Usalo cuando la regla se lea como "unico entre las filas donde X es cierto".