Contraintes d’unicité à la norme SQL ISO (acceptant le multi null)

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

Serveur : Msg 50000, Niveau  16, État 1, Procédure E_IU_TEST_UQ, Ligne 15
Violation de la contrainte d'unicité liée à la colonne COL2 de la table dbo.TEST_UQ. Transaction annulée.

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  * * * * *

MVP Microsoft SQL Server

2 réflexions au sujet de « Contraintes d’unicité à la norme SQL ISO (acceptant le multi null) »

  1. Avatar de sqlprosqlpro Auteur de l’article

    Il n’y a aucune raison pour que ce que vous faite ne « passe pas ». En effet un ordre SQL est une transaction par lui même. Du moment qu’en fin de traitement de l’ordre les contraintes sont vérifiées, le SGBDR doit effectuer la chose. Si ce n’est pas le cas, c’est parce que votre SGBDR n’est pas capable de réaliser des transactions. C’est donc une faute du SGBDR en question. je serais curieux de savoir sur quel pseudo SGBDR vous avez testé ceci.

    En tout cas, les tests que j’ai effectués sur différentes version de SQL Server ne posent aucun problème !

    INSERT INTO TEST_UQ (COL2) values ('A');&nbsp;<br />
    &nbsp;<br />
    INSERT INTO TEST_UQ (COL2) values ('AA');&nbsp;<br />
    &nbsp;<br />
    UPDATE TEST_UQ SET COL2 = COL2 + 'A';&nbsp;<br />
    &nbsp;<br />
    SELECT * FROM TEST_UQ;

    En voici le résultat sur MS SQL Server 2008, 2005, 2000 :

    COL1        COL2&nbsp;<br />
    ----------- ----------&nbsp;<br />
    1           AA&nbsp;<br />
    2           AAA

    Ne jamais oublier qu’un SGBDRne traite pas de manière itérative mais ensembliste. Lisez l’article que j’ai écrit sur les règles de Codd (http://sqlpro.developpez.com/SGBDR/ReglesCodd/ – discussion sur la règle 12 page 4) et notamment la nature ensemblistes des opérations dans les SGBDR. Un SGBDR qui ne serait pas capable de cela n’est pas un SGBDR et ce n’est pas moi qui le dit, mais l’inventeur des SGBDR : Frank edgar COdd !

    A +

  2. Avatar de azur668azur668

    Oui, mais…
    INSERT INTO TEST_UQ (COL2) values (‘A’);
    INSERT INTO TEST_UQ (COL2) values (‘AA’);
    UPDATE TEST_UQ SET COL2 = COL2 + ‘A';

    Ca ne passe pas, car on crée un doublon ‘temporaire’ en écrivant la valeur ‘AA’.
    Mais au final l’ancienne valeur ‘AA’ sera elle-même remplacée par ‘AAA’ et il n’y aura pas de doublon.
    Et avec un index unique ‘classique’ il ne teste que l’unicité du résultat final.

    (remarque que avec Access ca ne passe pas même avec un index Unique ‘standard’)

Laisser un commentaire