Réponse.

Historique de la question

Le contrôle de l'unicité des clés métier (pas seulement par ID, mais aussi par des champs « naturels ») est une préoccupation de longue date dans les applications d'entreprise. Souvent, ces clés peuvent être complexes (plusieurs colonnes) et admettent des valeurs NULL. Les outils standards de SQL — la contrainte UNIQUE ou les index uniques — ont des limitations : selon la norme SQL, plusieurs lignes où au moins un des composants de la clé UNIQUE est égal à NULL sont considérées comme uniques et ne violent pas la contrainte.

Problème

La logique métier peut exiger qu'un ensemble de colonnes (y compris les colonnes admettant NULL) soit unique dans son ensemble, tandis que le comportement de la norme SQL (NULL ≠ NULL) rompt ce scénario. Par exemple, nous avons une table avec une paire de colonnes (passport_number, passport_series), et au moins l'une d'elles peut être NULL, mais si les valeurs correspondent, nous devons interdire le doublon.

Solution

Dans la plupart des SGBD populaires, la solution consiste à réaliser le contrôle d'unicité via un trigger ou à utiliser des index partiels avec condition, ou à utiliser des fonctions permettant de considérer NULL comme des valeurs égales (par exemple, ISNULL ou COALESCE). Voici un exemple en PostgreSQL utilisant un index unique basé sur une expression :

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

Au niveau de la logique d'application, il est souvent nécessaire de dupliquer la vérification pour ne pas manquer de doublons lors de l'insertion.

Caractéristiques clés :

La contrainte UNIQUE standard ne fonctionne pas si la clé inclut au moins un NULL.
La solution dépend du SGBD : index partiels, colonnes calculées, triggers.
Le contrôle d'unicité peut être confié à la logique métier en l'absence de possibilité de créer un index correct.

Questions pièges.

Peut-on être sûr qu'un index unique garantit l'impossibilité d'insérer des doublons si les colonnes comprennent des colonnes nullable ?

Non, on ne peut pas. En SQL, le comportement avec NULL est particulier : les lignes avec les mêmes ensembles de valeurs, où au moins l'une d'elles est NULL, sont considérées comme différentes par l'index et permettent leur stockage simultané.

Les différents SGBD peuvent-ils implémenter NULL-unicité de manière différente ?

Oui, il existe des différences. Par exemple, dans Oracle, un index unique permet plusieurs lignes avec NULL, tandis que dans MS SQL, il n'en autorise qu'une seule. PostgreSQL peut créer des index partiels via des expressions.

Est-il possible de contourner le problème uniquement par le biais de DDL et sans triggers ?

Dans certains SGBD, oui, via des expressions, par exemple COALESCE. Mais ce n'est pas toujours possible, et si un comportement complexe est requis (NULL doit être considéré comme un doublon), il faudra utiliser des contrôles logiques ou des triggers.

Erreurs typiques et anti-modèles

Se fier à l'unique standard lorsque des NULL sont admissibles dans les colonnes
Ne pas vérifier les doublons au niveau de la logique métier
Créer des triggers sans tenir compte des performances

Exemple de la vie réelle

Cas négatif

Dans une base de données non critique, la décision a été prise de se fier à un index unique sur (email, code département), où les deux champs admettent NULL. En conséquence, des doublons sont régulièrement apparus, ce qui a perturbé les intégrations externes.

Avantages :

DDL simple

Inconvénients :

Doublons non évidents, données incohérentes, erreurs de rapports

Cas positif

Dans PostgreSQL, la clé métier avec les champs (numéro de passeport, série) a été implémentée via un index sur COALESCE, et une vérification a été ajoutée du côté de l'application.