Comment créer un index unique sur une colonne NULL?

en utilisant SQL Server 2008, vous pouvez créer un index filtré: http://msdn.microsoft.com/en-us/library/cc280372.aspx . (Je vois Simon a ajouté ceci comme un commentaire, mais a pensé qu'il méritait sa propre réponse comme le commentaire est facilement raté)

une autre option est un déclencheur pour vérifier l'unicité, mais cela pourrait affecter la performance.

le tour de colonne calculé est largement connu comme un "nullbuster"; mes notes crédit Steve Kass:

CREATE TABLE dupNulls (
pk int identity(1,1) primary key,
X  int NULL,
nullbuster as (case when X is null then pk else 0 end),
CONSTRAINT dupNulls_uqX UNIQUE (X,nullbuster)
)

à proprement parler, une colonne (ou un ensemble de colonnes) unique pouvant être nulle (ou un enregistrement de NULLs) ne peut être nulle qu'une seule fois, car avoir la même valeur (et ceci inclut NULL) plus d'une fois viole évidemment la contrainte unique.

cependant, cela ne signifie pas que le concept de "colonnes nulles uniques" est valide; pour l'implémenter réellement dans n'importe quelle base de données relationnelle, nous devons juste garder à l'esprit que ce genre de bases de données sont destinées à être normalisées pour fonctionner correctement, et la normalisation implique généralement l'ajout de plusieurs tables supplémentaires (non-entity) pour établir des relations entre les entités.

nous allons travailler un exemple de base en considérant seulement une" colonne unique nullable", il est facile de l'étendre à plus de telles colonnes.

supposons que nous l'information représentée par une table comme celle-ci:

create table the_entity_incorrect
(
  id integer,
  uniqnull integer null, /* we want this to be "unique and nullable" */
  primary key (id)
);

nous pouvons le faire en mettant uniqnull à part et en ajoutant une deuxième table pour établir une relation entre les valeurs uniqnull et l'entité_ (plutôt que d'avoir uniqnull "à l'intérieur de" l'entité_):

create table the_entity
(
  id integer,
  primary key(id)
);

create table the_relation
(
  the_entity_id integer not null,
  uniqnull integer not null,

  unique(the_entity_id),
  unique(uniqnull),
  /* primary key can be both or either of the_entity_id or uniqnull */
  primary key (the_entity_id, uniqnull), 
  foreign key (the_entity_id) references the_entity(id)
);

pour associer une valeur d'uniqnull à une ligne dans l'entity nous devons aussi ajouter une ligne dans la_relation.

pour les lignes de la_entity où aucune valeur uniqnull n'est associée (c'est-à-dire pour celles que nous mettrions NULL dans la_entity_incorrect), nous n'ajoutons simplement pas de ligne dans la_relation.

notez que les valeurs pour uniqnull seront uniques pour toute la_relation, et notez aussi que pour chaque valeur de l'entity il peut y avoir au plus une valeur dans la_relation, puisque les clés primaires et étrangères sur elle l'imposent.

alors, si une valeur de 5 pour uniqnull doit être associée à un identifiant the_entity de 3, nous devons:

start transaction;
insert into the_entity (id) values (3); 
insert into the_relation (the_entity_id, uniqnull) values (3, 5);
commit;

et, si une valeur d'id de 10 pour l'entité_ n'a pas de contrepartie uniqnull, nous ne faisons que:

start transaction;
insert into the_entity (id) values (10); 
commit;

pour dénormaliser cette information et obtenir les données qu'une table comme la_entity_incorrect contiendrait, nous devons:

select
  id, uniqnull
from
  the_entity left outer join the_relation
on
  the_entity.id = the_relation.the_entity_id
;

l'opérateur "jointure externe gauche" assure que toutes les lignes de the_entity apparaîtront dans le résultat, en mettant NULL dans la colonne uniqnull quand aucune colonne correspondante n'est présente dans la_relation.

rappelez-vous, tout effort passé pendant quelques jours (ou des semaines ou des mois) dans la conception d'une base de données bien normalisée (et les vues et les procédures de dénormalisation correspondantes) vous sauvera des années (ou des décennies) de souffrance et de gaspillage de ressources.