Respuesta.

Historia de la pregunta

El control de unicidad de claves de negocio (no solo por ID, sino también por campos "naturales") ha sido un tema recurrente en aplicaciones empresariales. A menudo, estas claves son complejas (varias columnas) y permiten valores NULL. Los mecanismos estándar de SQL — UNIQUE CONSTRAINT o índices únicos — tienen limitaciones: según el estándar SQL, varias filas donde al menos uno de los componentes de la clave UNIQUE es NULL se consideran únicas y no violan la restricción.

Problema

La lógica empresarial puede requerir que un conjunto de columnas (incluyendo las que permiten NULL) en conjunto sea único, y el comportamiento del estándar SQL (NULL ≠ NULL) rompe este escenario. Por ejemplo, tenemos una tabla con un par de columnas (número_de_pasaporte, serie_de_pasaporte), y al menos uno de ellos puede ser NULL, pero si los valores coinciden, debemos prohibir duplicados.

Solución

En la mayoría de los sistemas de gestión de bases de datos populares, la solución es implementar el control de unicidad a través de un trigger o utilizar índices parciales con condiciones, o usar funciones que permitan comparar NULL como valores iguales (por ejemplo, ISNULL o COALESCE). Aquí hay un ejemplo en PostgreSQL utilizando un índice único por expresión:

CREATE UNIQUE INDEX idx_passport_unique
ON persons (
  COALESCE(passport_number, ''),
  COALESCE(passport_series, '')
);

A nivel lógico de la aplicación, a menudo es necesario duplicar la verificación para no perder duplicados en la etapa de inserción.

Características clave:

El UNIQUE CONSTRAINT estándar no funciona si hay al menos un NULL en la clave.
La solución depende del DBMS: índices parciales, columnas calculadas, triggers.
El control de unicidad puede recaer en la lógica de negocio si no es posible crear un índice correcto.

Preguntas engañosas.

¿Se puede estar seguro de que un índice único garantiza la imposibilidad de insertar duplicados si hay columnas nullable?

No, no se puede. En SQL, el comportamiento con NULL es especial: las filas con el mismo conjunto de valores, donde al menos uno de ellos es NULL, son consideradas diferentes por el índice y permiten su almacenamiento simultáneo.

¿Pueden diferentes DBMS implementar la unicidad de NULL de manera diferente?

Sí, hay diferencias entre ellos. Por ejemplo, en Oracle, un índice único permite varias filas con NULL, mientras que en MS SQL solo una. PostgreSQL puede crear un índice parcial a través de expresiones.

¿Es posible sortear el problema solo a nivel de DDL y sin triggers?

En algunos sistemas de gestión de bases de datos, sí, a través de expresiones como COALESCE. Pero no siempre, y si se requiere un comportamiento complejo (NULL debe considerarse un duplicado), se deberá utilizar verificaciones lógicas o triggers.

Errores típicos y anti-patrones

Confiar en el UNIQUE estándar cuando las columnas permiten NULL
No verificar duplicados a nivel de lógica de negocio
Crear triggers sin considerar el rendimiento

Ejemplo de la vida real

Caso negativo

En una base de datos no crítica, se decidió confiar en un índice único basado en (email, código de departamento), donde ambos campos permiten NULL. Como resultado, aparecieron duplicados repetidamente, lo que rompió las integraciones externas.

Pros:

DDL simple

Contras:

Duplicados poco evidentes, datos inconsistentes, errores en informes

Caso positivo

En PostgreSQL, la clave de negocio con campos (número de pasaporte, serie) se implementó a través de un índice de COALESCE, y además se agregó una verificación del lado de la aplicación.