Respuesta.

En bases de datos relacionales, la tarea de garantizar la unicidad a menudo recae en las restricciones UNIQUE. Sin embargo, en la práctica empresarial, hay situaciones en las que se requiere unicidad en una combinación de campos, algunos de los cuales pueden ser NULL (por ejemplo, una combinación única de email+teléfono, donde el teléfono puede ser desconocido).

Historia de la pregunta: En SQL, las restricciones UNIQUE estándar no garantizan la unicidad cuando una de las columnas es NULL; la especificación considera que dichos valores son mutuamente excluyentes.

Problema: Para claves complejas con NULL, las restricciones estándar conducen a la aparición de duplicados. Esto es particularmente crítico cuando la integridad de los datos es necesaria: al importar, migrar o realizar actualizaciones masivas.

Solución: Utilizar una columna calculada que tenga en cuenta todos los valores (incluyendo NULL) y aplicar la unicidad a esa columna o utilizar lógica de triggers.

Ejemplo de código:

ALTER TABLE my_table
ADD computed_uniqueness AS (
   ISNULL(email, '') + '#' + ISNULL(phone, '')
);   
CREATE UNIQUE INDEX idx_my_table_computed_uniqueness ON my_table(computed_uniqueness);

O (para PostgreSQL, donde hay expresiones en el índice y NULL se considera diferente):

CREATE UNIQUE INDEX idx_unique_email_phone ON my_table ((COALESCE(email, '##')),(COALESCE(phone, '##')));

Características clave:

Gestión explícita de la regla de unicidad para combinaciones con NULL.
Posibilidad de automatización y soporte a nivel de la base de datos.
Soporte incluso para esquemas complejos y cambiantes.

Preguntas engañosas.

¿Se puede usar un índice UNIQUE normal para garantizar la unicidad si parte de las columnas permite NULL?

No. Según ANSI SQL, un índice UNIQUE permite múltiples filas donde al menos una de las columnas en la combinación es NULL, ya que NULL no se considera igual a ningún otro valor, incluido NULL.

¿Cuál es la diferencia entre usar un índice único en una expresión y usar un trigger BEFORE INSERT para verificar la unicidad?

Un índice único es más fácil de mantener y más rápido en su funcionamiento, pero no siempre puede implementar reglas comerciales complejas (por ejemplo, excepciones o combinaciones personalizadas). Un trigger es más flexible, pero más lento y complicado de mantener.

Ejemplo:

CREATE OR REPLACE FUNCTION check_custom_unique()
RETURNS TRIGGER AS $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM my_table
    WHERE COALESCE(NEW.email, '##') = COALESCE(email, '##')
      AND COALESCE(NEW.phone, '##') = COALESCE(phone, '##')
  ) THEN
    RAISE EXCEPTION 'Se encontró un duplicado';
  END IF;
  RETURN NEW;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER trg_custom_unique
BEFORE INSERT OR UPDATE ON my_table
FOR EACH ROW EXECUTE FUNCTION check_custom_unique();

¿Se puede usar DISTINCT en SELECT para resolver el problema de unicidad a nivel de aplicación?

Sí, pero solo para consultas; esto no previene la inserción de duplicados al modificar los datos y no es un sustituto de las restricciones a nivel de tabla.

Errores comunes y anti-patrones

Intentos de usar UNIQUE CONSTRAINT donde la clave es una combinación que puede contener NULL.
Verificación de unicidad solo a nivel de la aplicación (eludiendo la base de datos).
Complejidad excesiva en triggers sin índices explícitos: pérdida de rendimiento.

Ejemplo de la vida real

Caso negativo

Una empresa implementa la unicidad de email+teléfono a través de UNIQUE(email, phone). En la base de datos aparecen duplicados por email si phone=NULL.

Ventajas:

Sencillez de implementación.

Desventajas:

Pérdida de integridad, duplicados.
Errores implícitos, difíciles de rastrear en el análisis.

Caso positivo

Se utiliza una columna calculada (COALESCE(email, '##') + '#' + COALESCE(phone, '')), sobre la que se aplica un índice único.