Depuis des lustres, SQL Server ne permets toujours pas de réaliser des contraintes d’unicité respectant la norme ISO. En effet, la norme SQL impose qu’une contrainte d’unicité porte sur les valeurs exprimées. Ce qui signifie qu’une multiplicité des marqueurs NULLs est permise dans une colonne UNIQUE. Voir :

http://sqlpro.developpez.com/cours/sqlaz/ddl/?page=partie2#L7.2.2

Or SQL Server n’accepte pas cela et refuse d’insérer plus d’une fois le marqueur NULL dans une colonne pourvue d’une contrainte d’unicité… (pour une étude sur les NULLs, voir : http://sqlpro.developpez.com/cours/null/)
C’est le défaut majeur de SQL Server et l’on peut s’étonner qu’il persiste depuis des lustres… Mais comme le contrôle de cette unicité repose sur le moteur de stockage, Microsoft refuse cette correction qui pourrait s’avérer très dangereuse. En effet autant on peut modifier à souhait le moteur relationnel, car même si quelques bugs y seront introduits il ne portent pas à conséquence au regard d’une erreur au moment du stockage de la donnée !

Voici donc différentes solutions de contournement de ce problème…

1 – une fausse bonne idée : unicité via une colonne calculée…

Dans un article un peu farfelu, un internaute cosmoplanétaire nous invite à contourner ce problème en utilisant une colonne calculée ou il remplace à la volée le NULL de la colonne en choisissant une autre colonne, toujours valuée et toujours unique, comme, par exemple, une clef auto incrémentée :

http://decipherinfosys.wordpress.com/2007/11/30/multiple-null-values-in-a-unique-index-in-sql-serverdb2-luw/

Reprenons l’exemple de cet article et étoffons le (mais ne faisons pas un INDEX UNIQUE, cela n’a pas de sens !, faisons une contrainte SQL…) :

CREATE DATABASE UNIC 

GO 

 

USE UNIC 

GO 

 

CREATE TABLE TEST_UQ  

(COL1 INT IDENTITY(1,1) PRIMARY KEY,  

 COL2 NVARCHAR(10) NULL CONSTRAINT UQ UNIQUE) 

GO 

 

INSERT INTO TEST_UQ (COL2) values ('abc'); 

INSERT INTO TEST_UQ (COL2) values ('xyz'); 

INSERT INTO TEST_UQ (COL2) values (Null);

Une nouvelle tentative d’insertion d’un marqueur NULL dans la colonnes COL2 provoque instantanément le viol de la contrainte ;

INSERT INTO TEST_UQ (COL2) values (Null); 

 

Serveur : Msg 2627, Niveau 14, État 2, Ligne 1 

Violation de la contrainte UNIQUE KEY 'UQ'. Impossible d'insérer une clé en double dans l'objet 'TEST_UQ'. 

L'instruction a été arrêtée.

Comme cet auteur nous le conseille supprimons la contrainte d’unicité :

ALTER TABLE TEST_UQ DROP CONSTRAINT UQ

Voyons voir ce que donne le truc de cet auteur à base de colonne calculée…

ALTER TABLE TEST_UQ  

ADD COL3 AS (CASE  

                WHEN COL2 IS NULL THEN CAST(COL1 AS NVARCHAR(10))  

                ELSE COL2  

             END);

Rajoutons maintenant la contrainte d’unicité sur la colonne calculée :

ALTER TABLE TEST_UQ 

ADD CONSTRAINT UQ UNIQUE (COL3)

Vous pouvez maintenant constater que la colonne respecte bien l’unicité désirée, c’est à dire l’unicité des seules valeurs exprimées, et la multiplicité des NULLs :

INSERT INTO TEST_UQ (COL2) values (Null); 

INSERT INTO TEST_UQ (COL2) values (Null); 

INSERT INTO TEST_UQ (COL2) values (Null); 

 

SELECT * 

FROM   TEST_UQ; 

 

COL1        COL2       COL3        

----------- ---------- ----------  

1           abc        abc 

2           xyz        xyz 

3           NULL       3 

5           NULL       5 

6           NULL       6 

7           NULL       7

Bravo !!!
Sauf que cette solution farfelue est complétement idiote… En effet l’insertion suivante :

INSERT INTO TEST_UQ (COL2) values (3);

Plante lamentablement tout simplement parce qu’il existe un autoincrément de valeur 3 dans COL1 alors que c’est bien une valeur unique dans la colonne COL2…

2 – un déclencheur associé à un index

En définitif le seul moyen de réaliser cela est d’utiliser un trigger qui compte les lignes de la table par groupage sur les colonnes de l’unicité et lance un ROLLBACK si ce comptage excéde la valeur 1.
Attention, pour être efficace, un tel mécanisme doit impérativement être accompagné d’un index sur la (ou les) colonne(s) visée(s).

Voici le script réalisant un index unique à la norme SQL avec SQL Server :

USE master; 

GO 

 

IF EXISTS (SELECT * 

           FROM   sysdatabases  

           WHERE  name = 'UNIC') 

   DROP DATABASE UNIC 

GO 

 

CREATE DATABASE UNIC 

GO 

 

USE UNIC 

GO 

 

CREATE TABLE TEST_UQ  

(COL1 INT IDENTITY(1,1) PRIMARY KEY,  

 COL2 NVARCHAR(10) NULL) 

GO 

 

CREATE INDEX X ON TEST_UQ (COL2); 

GO 

 

CREATE TRIGGER E_IU_TEST_UQ 

ON TEST_UQ 

FOR INSERT, UPDATE 

AS 

 

IF EXISTS(SELECT 0 

          FROM   inserted AS i 

                 INNER JOIN TEST_UQ as u 

                       ON i.COL2 = u.COL2 

          GROUP  BY u.COL2 

          HAVING COUNT(u.COL2) > 1) 

   BEGIN 

      ROLLBACK 

      RAISERROR('Violation de la contrainte d''unicité liée à la colonne COL2 de la table dbo.TEST_UQ. Transaction annulée.', 16, 1) 

   END 

GO

Et maintenant l’heure de vérité… Le test !

INSERT INTO TEST_UQ (COL2) values ('abc'); 

INSERT INTO TEST_UQ (COL2) values ('xyz'); 

INSERT INTO TEST_UQ (COL2) values (Null); 

 

INSERT INTO TEST_UQ (COL2) values (Null);

Tout s’est bien passé jusqu’ici…

INSERT INTO TEST_UQ (COL2) values ('abc');

Réaction logique et attendue…

SELECT * FROM TEST_UQ

COL1        COL2        

----------- ----------  

3           NULL 

4           NULL 

1           abc 

2           xyz

CQFD !

3 Рun index unique filtr̩

Depuis la version 2008 de SQL Server, il est possible de créer des index unique filtrés. Ce qui conduit à une solution plus simple :

USE master;  

GO  

 

IF EXISTS (SELECT *  

           FROM   sysdatabases   

           WHERE  name = 'UNIC')  

   DROP DATABASE UNIC  

GO  

 

CREATE DATABASE UNIC  

GO  

 

USE UNIC  

GO  

 

CREATE TABLE TEST_UQ   

(COL1 INT IDENTITY(1,1) PRIMARY KEY,   

 COL2 NVARCHAR(10) NULL)  

GO

Et maintenant implantons l’index unique filtré sur les valeurs à l’exception des « non » valeurs :

CREATE UNIQUE INDEX X_U_C2  

   ON TEST_UQ  (COL2) 

   WHERE (COL2 IS NOT NULL);

Enfin, s’il y a plusieurs colonnes, le principe est le meme :

CREATE TABLE TEST_UQ2   

(COL1 INT IDENTITY(1,1) PRIMARY KEY,   

 COL2 NVARCHAR(10) NULL, 

 COL3 NVARCHAR(10) NULL)  

GO  

 

CREATE UNIQUE INDEX X_U_C23  

   ON TEST_UQ2  (COL2, COL3) 

   WHERE (COL2 IS NOT NULL AND COL3 IS NOT NULL);

Et maintenant le test de vérité :

INSERT INTO TEST_UQ2 (COL2, COL3) values ('abc', 'abc');  

INSERT INTO TEST_UQ2 (COL2, COL3) values ('abc', 'def');  

INSERT INTO TEST_UQ2 (COL2, COL3) values ('abc',  NULL);  

INSERT INTO TEST_UQ2 (COL2, COL3) values ('abc',  NULL);  

INSERT INTO TEST_UQ2 (COL2, COL3) values (NULL,  'abc');  

INSERT INTO TEST_UQ2 (COL2, COL3) values (NULL,  'abc');  

INSERT INTO TEST_UQ2 (COL2, COL3) values (NULL,  NULL);  

INSERT INTO TEST_UQ2 (COL2, COL3) values (NULL,  NULL);

Tout s’est bien passé jusqu’ici…

INSERT INTO TEST_UQ2 (COL2, COL3) values ('abc', 'abc');

Msg 2601, Niveau 14, État 1, Ligne 1
Impossible d’insérer une ligne de clé en double dans l’objet ‘dbo.TEST_UQ2′ avec un index unique ‘X_U_C23′.
L’instruction a été arrêtée.

C’est bien ce qu’il fallait !

Contenu final :

SELECT * FROM TEST_UQ2

COL1        COL2       COL3 

----------- ---------- ---------- 

1           abc        abc 

2           abc        def 

3           abc        NULL 

4           abc        NULL 

5           NULL       abc 

6           NULL       abc 

7           NULL       NULL 

8           NULL       NULL

CQFD bis !!!

-------- 

Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/ 

Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server 

www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation 

* * * * *  Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence  * * * * *