PostgreSQL
Claves e identificadores
Diseño de claves naturales, surrogate, compuestas y públicas con bigint, identity, UUID y restricciones de unicidad.
- Última actualización
- Actualizada
- Nivel
- Fundamentos
PostgreSQL
Diseño de claves naturales, surrogate, compuestas y públicas con bigint, identity, UUID y restricciones de unicidad.
bigintEl diseño de claves responde tres preguntas diferentes:
¿Cómo identifica el dominio este hecho?
→ natural key
¿Cómo lo referencia internamente el sistema?
→ primary/surrogate key
¿Cómo se expone fuera del sistema?
→ public identifierA veces una sola clave cumple las tres responsabilidades. En otros casos conviene separarlas para evitar acoplar identidad interna, reglas del negocio y contrato público.
Sin una estrategia clara aparecen fallos como:
Cualquier conjunto de atributos que identifica de forma única, aunque incluya columnas innecesarias.
Superkey mínima: si eliminas una columna, deja de ser única.
Candidate key elegida como identidad principal de la tabla.
Candidate key no elegida como primary, normalmente protegida con UNIQUE.
Surge del dominio: (country_code, tax_id), (business_id, sku), ISBN.
Identificador técnico sin significado externo: sequence, bigint identity o UUID generado.
Clave formada por varias columnas.
CREATE TABLE currencies (
code char(3) PRIMARY KEY,
name text NOT NULL
);Aquí code es una natural key estable.
En otra tabla:
CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE
);La primary key es surrogate bigint, y public_id es un identificador externo alternativo.
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEYVentajas:
Costes:
CREATE SEQUENCE order_id_seq;
SELECT nextval('order_id_seq');Una sequence es un objeto independiente optimizado para concurrencia. nextval avanza fuera del rollback transaccional normal.
Ejemplo:
transaction A obtiene 100
transaction A hace ROLLBACK
siguiente transaction obtiene 101El hueco es esperado.
También pueden aparecer huecos por:
No uses max(id) como conteo ni exijas continuidad para facturación regulada.
serial crea implícitamente una sequence y default. Identity comunica mejor la relación entre columna y generación y sigue el estándar SQL.
Para diseños nuevos:
GENERATED ALWAYS AS IDENTITYes normalmente preferible.
BY DEFAULT permite valores explícitos; ALWAYS los rechaza salvo override. La elección depende de imports y control de generación.
id uuid PRIMARY KEY DEFAULT gen_random_uuid()Ventajas:
Costes:
Aleatorio. Distribuye inserts por el B-tree, lo que puede aumentar page splits y reducir cache locality.
Incluye componente temporal y suele mejorar orden/localidad.
PostgreSQL 18 incorpora funciones core relacionadas con UUID v4/v7. En versiones anteriores, la función disponible puede depender de pgcrypto, uuid-ossp, aplicación o extensión adicional.
No documentes uuidv7() como universal sin marcar versión.
ULID y otros formatos pueden ofrecer orden temporal y representación textual. PostgreSQL no los trata automáticamente como tipo nativo estándar; suelen almacenarse como UUID, bytea o text mediante extensión/lógica propia.
Evalúa:
Requisito:
Un SKU identifica un producto dentro de un business.
UNIQUE (business_id, sku)Puede ser la primary key:
PRIMARY KEY (business_id, sku)O una alternate key junto a surrogate ID:
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
UNIQUE (business_id, sku)La surrogate facilita FKs y cambios de SKU; la natural constraint conserva integridad.
Ejemplos potenciales: ISO currency code, country code, una clave de catálogo bien gobernada.
Un email no suele ser buena primary key porque puede cambiar y su canonicalización es compleja.
Tabla asociativa:
CREATE TABLE user_branches (
user_id bigint NOT NULL REFERENCES users(id),
branch_id bigint NOT NULL REFERENCES branches(id),
role text NOT NULL,
PRIMARY KEY (user_id, branch_id)
);La combinación representa una membership única.
Añadir id:
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
UNIQUE (user_id, branch_id)puede justificarse si otras tablas referencian la membership, pero no elimina la unique natural.
PRIMARY KEY (tenant_id, id)Ventajas:
Costes:
Alternativa:
tenant_id separado.CREATE TABLE orders (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
public_id uuid NOT NULL DEFAULT gen_random_uuid() UNIQUE,
...
);Beneficios:
Costes:
No lo añadas por defecto a todas las tablas.
Un bigint predecible permite probar /orders/101, /orders/102. Un UUID dificulta adivinar IDs, pero la API sigue necesitando:
actor autenticado
+
resource ownership/permission
+
tenant scope“Difícil de adivinar” no es “autorizado”.
Un slug puede ser una identidad pública legible:
slug text NOT NULL,
UNIQUE (business_id, slug)Debe definirse:
Si el slug cambia, puede convenir mantener un ID estable interno y tabla de aliases.
Un proveedor puede entregar stripe_payment_intent_id.
Diseño:
provider text NOT NULL,
external_id text NOT NULL,
UNIQUE (provider, external_id)No asumas que un ID externo es global entre proveedores o cuentas.
También considera longitud, formato y posibilidad de reutilización documentada por el proveedor.
Ventajas:
Riesgos:
Ventajas:
Riesgos:
RETURNING.INSERT INTO orders(customer_id)
VALUES ($1)
RETURNING id, public_id, created_at;Evita una consulta posterior y devuelve valores generados por defaults/triggers.
Soft delete:
deleted_at timestamptz¿Puede reutilizarse email o slug?
Opciones:
UNIQUE (email)Nunca se reutiliza.
CREATE UNIQUE INDEX users_active_email_uidx
ON users(lower(email))
WHERE deleted_at IS NULL;Se reutiliza tras soft delete.
La decisión es de dominio y seguridad; reutilizar identidad puede confundir auditoría o recuperación de cuenta.
En tablas grandes, cambiar PK y todas las FKs puede ser costoso.
Prevención: utiliza bigint cuando el crecimiento razonable puede superar integer.
Migración puede requerir:
No esperes llegar al límite.
Si insertas IDs explícitos, la sequence puede quedar detrás:
SELECT setval(
pg_get_serial_sequence('orders', 'id'),
(SELECT max(id) FROM orders)
);Ajusta con cuidado y comprende si el próximo valor debe ser max o max+1 según is_called.
Es extremadamente improbable con generador correcto, pero UNIQUE sigue siendo necesaria.
Una candidate key normalmente no debería aceptar NULL. UNIQUE por defecto puede permitir varios; usa NOT NULL o NULLS NOT DISTINCT según semántica.
Email/slug necesitan canonicalización e índice de expresión o tipo/collation apropiado.
Un ID que incluye país, año y secuencia se vuelve difícil de cambiar y puede filtrar información. Separa atributos del identificador cuando sea posible.
Los huecos son normales. Un consecutivo regulado necesita proceso propio, serialización y reglas de negocio.
No resuelve queries, sharding ni autorización y aumenta tamaño.
Propaga un valor mutable y sensible.
Permite duplicados semánticos.
La autorización debe ser independiente.
Puede esconder la identidad real y olvidar la unique compuesta.
Relaciones y cardinalidad utiliza claves y foreign keys para representar uno-a-uno, uno-a-muchos, muchos-a-muchos, jerarquías y ownership.