Toute contrainte d’unicité entraîne la création implicite d’une index unique sur la table, dont la clé est constituée des colonnes spécifiées dans la contrainte d’unicité.
Or, lors de l’insertion d’une nouvelle ligne ou de la modification d’une des colonnes constituant la clé unique, SQL Server considère NULL comme une valeur.
Cela est faux puisque NULL n’est pas une valeur : c’est l’absence de valeur.
Avec SQL Server 2008 ont été introduits les index filtrés, qui permettent de spécifier les lignes candidates à l’indexation, par l’ajout d’une simple clause WHERE.
Dès lors, il est possible de contourner le problème posé par les contraintes d’unicité en se servant de ce type d’index …
Supposons que nous avons plusieurs applications qui doivent se connecter à notre base de données, et que l’authentification des utilisateurs est gérée dans cette base de données à l’aide de la table suivante :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | CREATE TABLE dbo.TbUtilisateur ( IDUtilisateur INT NOT NULL IDENTITY CONSTRAINT PK_TbUtilisateur PRIMARY KEY, nomUtilisateur VARCHAR(30) NOT NULL, prenomUtilisateur VARCHAR(30) NOT NULL, identifiantUtilisateur VARCHAR(10) CONSTRAINT UQ_TbUtilisateur_identifiantUtilisateur UNIQUE, motDePasseUtilisateur VARCHAR(10) ) ALTER TABLE dbo.TbUtilisateur ADD CONSTRAINT CHK_TbUtilisateur_identifiantUtilisateur_motDePasseUtilisateur CHECK ( ( identifiantUtilisateur IS NULL AND motDePasseUtilisateur IS NULL ) OR ( identifiantUtilisateur IS NOT NULL AND motDePasseUtilisateur IS NOT NULL ) ) |
Il est nécessaire que l’identifiant qui permet à nos utilisateurs de se connecter soit unique.
Notre application donne le droit aux administrateurs d’enregistrer les nouveaux salariés.
Lors de la première connexion de ceux-ci, il doivent spécifier un identifiant et un mot de passe que les administrateurs ne peuvent pas choisir à leur place, et il a été décidé que ces deux valeurs seront laissées à NULL jusqu’à la première connexion de l’utilisateur.
Le contenu de la table pourrait dont être le suivant :
Essayons maintenant d’enregistrer un nouvel utilisateur :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | INSERT INTO dbo.TbUtilisateur ( nomUtilisateur, prenomUtilisateur, identifiantUtilisateur, motDePasseUtilisateur ) VALUES ( 'DELANEY', 'Kalen', NULL, NULL ) |
Cela provoque l’erreur suivante :
Msg 2627, Niveau 14, État 1, Ligne 1
Violation de la contrainte UNIQUE KEY ‘UQ_TbUtilisateur_identifiantUtilisateur’. Impossible d’insérer une clé en double dans l’objet ‘dbo.TbUtilisateur’.
L’instruction a été arrêtée.
Remplaçons donc la contrainte d’unicité par un index filtré :
1 2 3 4 5 6 7 8 9 10 | ------------------------------- -- Nicolas SOUQUET - 29/10/2009 ------------------------------- ALTER TABLE dbo.TbUtilisateur DROP CONSTRAINT UQ_TbUtilisateur_identifiantUtilisateur GO CREATE UNIQUE NONCLUSTERED INDEX IUXQ_TbUtilisateur_F_identifiantUtilisateur ON dbo.TbUtilisateur(identifiantUtilisateur) WHERE identifiantUtilisateur IS NOT NULL |
Tentons de nouveau l’insertion de ce nouvel utilisateur :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | INSERT INTO dbo.TbUtilisateur ( nomUtilisateur, prenomUtilisateur, identifiantUtilisateur, motDePasseUtilisateur ) VALUES ( 'DELANEY', 'Kalen', NULL, NULL ) |
Pour vérifier, tentons d’insérer un nouvel utilisateur avec le même identifiant de connexion que M. Brouard :
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | INSERT INTO dbo.TbUtilisateur ( nomUtilisateur, prenomUtilisateur, identifiantUtilisateur, motDePasseUtilisateur ) VALUES ( 'RANDAL', 'Paul', 'FB', '***' ) |
Cela provoque l’erreur suivante :
Msg 2601, Niveau 14, État 1, Ligne 1
Impossible d’insérer une ligne de clé en double dans l’objet ‘dbo.TbUtilisateur’ avec un index unique ‘IUXQ_TbUtilisateur_F_identifiantUtilisateur’.
L’instruction a été arrêtée.
L’unicité des identifiants de connexion est donc bien vérifiée, et laisse la place aux nouveaux entrants 
ElSuket