Restricciones UNIQUE y claves compuestas

UNIQUE: no se permiten duplicados

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  email VARCHAR(255) NOT NULL UNIQUE,
  username VARCHAR(50) UNIQUE
);

UNIQUE crea un índice

-- This shows the implicit index:
SELECT indexname FROM pg_indexes WHERE tablename = 'users';

UNIQUE compuesto

CREATE TABLE memberships (
  user_id BIGINT NOT NULL,
  group_id BIGINT NOT NULL,
  role TEXT NOT NULL,
  UNIQUE (user_id, group_id)        -- a user can be in a group at most once
);

UNIQUE frente a PRIMARY KEY

NULL en UNIQUE

CREATE TABLE invites (email VARCHAR(255) UNIQUE);
INSERT INTO invites VALUES (NULL), (NULL);   -- both succeed

-- PostgreSQL 15+ offers UNIQUE NULLS NOT DISTINCT to treat NULL as equal:
CREATE TABLE invites2 (email VARCHAR(255) UNIQUE NULLS NOT DISTINCT);

Clave primaria compuesta

CREATE TABLE order_items (
  order_id   BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id BIGINT NOT NULL REFERENCES products(id),
  quantity   INT NOT NULL CHECK (quantity > 0),
  PRIMARY KEY (order_id, product_id)
);

Clave sustituta + clave natural UNIQUE

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  sku VARCHAR(50) NOT NULL UNIQUE,
  name TEXT NOT NULL
);

Índices UNIQUE parciales

-- One active email per user (deleted users can re-register):
CREATE UNIQUE INDEX users_active_email_idx
  ON users (email)
  WHERE deleted_at IS NULL;

Índices UNIQUE sobre expresiones

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

Errores de violación de UNIQUE

ERROR:  duplicate key value violates unique constraint "users_email_key"
DETAIL:  Key (email)=(alice@example.com) already exists.

UPSERT se basa en UNIQUE

Resumen

Comprobación rápida