Réponse.

Dans les bases de données relationnelles, la tâche d'assurer l'unicité repose souvent sur les contraintes UNIQUE. Cependant, dans la pratique commerciale, il existe des situations où l'unicité est requise pour une combinaison de champs, dont certains peuvent être NULL (par exemple, une combinaison unique email + téléphone, mais le téléphone peut être inconnu).

Historique de la question : Dans SQL, les contraintes UNIQUE standard ne garantissent pas l'unicité lorsque l'une des colonnes est NULL — la spécification considère de telles valeurs comme étant mutuellement exclusives.

Problème : Pour des clés complexes avec NULL, les contraintes standard entraînent l'apparition de doublons. Cela est particulièrement critique lorsque l'intégrité des données est nécessaire : lors de l'importation, de la migration, ou des mises à jour massives.

Solution : Utiliser une colonne calculée qui prend en compte toutes les valeurs (y compris NULL) et appliquer l'unicité sur cette colonne, ou utiliser une logique de déclencheur.

Exemple de code :

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

Ou (pour PostgreSQL, où des expressions peuvent être utilisées dans l'index et NULL est considéré comme distinct) :

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

Points clés :

Gestion explicite de la règle d'unicité pour les combinaisons avec NULL.
Possibilité d'automatisation et de support au niveau de la base de données.
Support même pour des schémas complexes et modifiables.

Questions pièges.

Peut-on utiliser un index UNIQUE standard pour garantir l'unicité si certains colonnes acceptent NULL ?

Non. Selon le SQL ANSI, un index UNIQUE autorise plusieurs lignes où au moins l'une des colonnes dans la combinaison est NULL, car NULL n'est pas considéré comme égal à aucune autre valeur, y compris NULL.

Quelle est la différence entre l'utilisation d'un index unique sur une expression et l'utilisation d'un déclencheur BEFORE INSERT pour vérifier l'unicité ?

L'index unique est plus facile à entretenir et plus rapide, mais ne peut pas toujours mettre en œuvre des règles commerciales complexes (par exemple, des exceptions ou des combinaisons personnalisées). Le déclencheur est plus flexible, mais plus lent et plus compliqué à maintenir.

Exemple :

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 'Duplicate found';
  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();

Peut-on utiliser DISTINCT dans SELECT pour résoudre le problème de l'unicité au niveau de l'application ?

On peut, mais seulement pour la sélection — cela ne prévient pas l'introduction de doublons lors des modifications des données et ne remplace pas les contraintes au niveau de la table.

Erreurs typiques et anti-modèles

Tentatives d'utiliser une CONTRAINTE UNIQUE là où la clé est une combinaison avec des NULL possibles.
Vérification de l'unicité uniquement au niveau de l'application (en contournant la base de données).
Complexité excessive des déclencheurs sans index explicites — perte de performance.

Exemple tiré de la vie

Cas négatif

Une entreprise met en œuvre l'unicité email + téléphone via UNIQUE(email, phone). Des doublons apparaissent dans la base pour email si phone = NULL.

Avantages :

Simplicité de mise en œuvre.

Inconvénients :

Perte d'intégrité, doublons.
Erreurs implicites, difficilement traçables dans l'analyse.

Cas positif

Une colonne calculée est utilisée (COALESCE(email, '##') + '#' + COALESCE(phone, '')), sur celle-ci un index unique est appliqué.