Question Comment créer une contrainte unique qui autorise également les valeurs nulles?


Je veux avoir une contrainte unique sur une colonne que je vais remplir avec des GUID. Cependant, mes données contiennent des valeurs nulles pour ces colonnes. Comment créer la contrainte autorisant plusieurs valeurs NULL?

Voici un exemple de scénario. Considérez ce schéma:

CREATE TABLE People (
  Id INT CONSTRAINT PK_MyTable PRIMARY KEY IDENTITY,
  Name NVARCHAR(250) NOT NULL,
  LibraryCardId UNIQUEIDENTIFIER NULL,
  CONSTRAINT UQ_People_LibraryCardId UNIQUE (LibraryCardId)
)

Ensuite, voir ce code pour ce que j'essaie de réaliser:

-- This works fine:
INSERT INTO People (Name, LibraryCardId) 
 VALUES ('John Doe', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA');

-- This also works fine, obviously:
INSERT INTO People (Name, LibraryCardId) 
VALUES ('Marie Doe', 'BBBBBBBB-BBBB-BBBB-BBBB-BBBBBBBBBBBB');

-- This would *correctly* fail:
--INSERT INTO People (Name, LibraryCardId) 
--VALUES ('John Doe the Second', 'AAAAAAAA-AAAA-AAAA-AAAA-AAAAAAAAAAAA');

-- This works fine this one first time:
INSERT INTO People (Name, LibraryCardId) 
VALUES ('Richard Roe', NULL);

-- THE PROBLEM: This fails even though I'd like to be able to do this:
INSERT INTO People (Name, LibraryCardId) 
VALUES ('Marcus Roe', NULL);

La déclaration finale échoue avec un message:

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

Comment puis-je modifier ma contrainte de schéma et / ou d'unicité de manière à autoriser plusieurs NULL valeurs, tout en vérifiant l'unicité des données réelles?


495
2018-04-20 10:12


origine


Réponses:


SQL Server 2008 +

Vous pouvez créer un index unique qui accepte plusieurs NULL avec un WHERE clause. Voir le Réponse ci-dessous.

Avant SQL Server 2008

Vous ne pouvez pas créer une contrainte UNIQUE et autoriser les valeurs NULL. Vous devez définir une valeur par défaut de NEWID ().

Mettez à jour les valeurs existantes à NEWID () où NULL avant de créer la contrainte UNIQUE.


110
2018-04-20 10:20



Ce que vous recherchez fait en effet partie des normes ANSI SQL: 92, SQL: 1999 et SQL: 2003, c’est-à-dire qu’une contrainte UNIQUE doit interdire les valeurs non NULL en double mais accepter plusieurs valeurs NULL.

Dans le monde Microsoft de SQL Server cependant, une seule valeur NULL est autorisée mais plusieurs valeurs NULL ne sont pas ...

Dans SQL Server 2008, vous pouvez définir un index filtré unique basé sur un prédicat qui exclut les valeurs NULL:

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

Dans les versions antérieures, vous pouvez recourir à VIEWS avec un prédicat NOT NULL pour appliquer la contrainte.


1117
2018-04-20 10:31



SQL Server 2008 et plus

Il suffit de filtrer un index unique:

CREATE UNIQUE NONCLUSTERED INDEX UQ_Party_SamAccountName
ON dbo.Party(SamAccountName)
WHERE SamAccountName IS NOT NULL;

Dans les versions inférieures, une vue matérialisée n'est toujours pas requise

Pour SQL Server 2005 et versions antérieures, vous pouvez le faire sans affichage. Je viens d'ajouter une contrainte unique comme vous le demandez à l'un de mes tableaux. Étant donné que je veux l'unicité dans la colonne SamAccountName, mais je veux autoriser plusieurs valeurs NULL, j'ai utilisé une colonne matérialisée plutôt qu'une vue matérialisée:

ALTER TABLE dbo.Party ADD SamAccountNameUnique
   AS (Coalesce(SamAccountName, Convert(varchar(11), PartyID)))
ALTER TABLE dbo.Party ADD CONSTRAINT UQ_Party_SamAccountName
   UNIQUE (SamAccountNameUnique)

Vous devez simplement mettre quelque chose dans la colonne calculée qui sera garanti unique sur toute la table lorsque la colonne unique souhaitée est NULL. Dans ce cas, PartyID est une colonne d'identité et étant numérique ne correspondra jamais SamAccountName, alors ça a marché pour moi. Vous pouvez essayer votre propre méthode. Assurez-vous de bien comprendre le domaine de vos données afin d'éviter toute intersection avec des données réelles. Cela pourrait être aussi simple que d'ajouter un caractère différentiateur comme ceci:

Coalesce('n' + SamAccountName, 'p' + Convert(varchar(11), PartyID))

Même si PartyID est devenu un jour non numérique et pourrait coïncider avec un SamAccountName, maintenant ça n'aura plus d'importance.

Notez que la présence d'un index incluant la colonne calculée entraîne implicitement l'enregistrement de chaque résultat d'expression sur le disque avec les autres données de la table, ce qui nécessite un espace disque supplémentaire.

Notez que si vous ne voulez pas d'index, vous pouvez toujours enregistrer le processeur en rendant l'expression précalculée sur le disque en ajoutant le mot clé PERSISTEDà la fin de la définition de l'expression de colonne.

Dans SQL Server 2008 et versions ultérieures, utilisez définitivement la solution filtrée si vous le pouvez!

Controverse

S'il vous plaît noter que certains professionnels de base de données verront cela comme un cas de "NULL de substitution", qui ont certainement des problèmes (principalement en raison de problèmes liés à essayer de déterminer quand quelque chose est un valeur réelle ou un valeur de remplacement pour les données manquantes; il peut aussi y avoir des problèmes avec le nombre de valeurs de substitution non NULL se multipliant comme si de rien n'était.

Cependant, je crois que ce cas est différent. La colonne calculée que j'ajoute ne sera jamais utilisée pour déterminer quoi que ce soit. Il n'a pas de sens de lui-même et ne code aucune information qui n'est pas déjà trouvée séparément dans d'autres colonnes correctement définies. Il ne devrait jamais être sélectionné ou utilisé.

Donc, mon histoire est que ce n'est pas un substitut NULL, et je le respecte! Étant donné que nous ne voulons pas réellement la valeur non NULL à d'autres fins que de tromper le UNIQUE index pour ignorer NULLs, notre cas d'utilisation n'a aucun des problèmes qui surviennent avec la création normale NULL de substitution.

Cela dit, je n'ai pas de problème avec l'utilisation d'une vue indexée à la place, mais cela pose certains problèmes, comme l'obligation d'utiliser SCHEMABINDING. Amusez-vous en ajoutant une nouvelle colonne à votre table de base (vous devrez au moins laisser tomber l'index, puis laisser tomber la vue ou modifier la vue pour ne pas être liée au schéma). Voir le long (long) liste des conditions requises pour la création d'une vue indexée dans SQL Server (2005) (également versions ultérieures), (2000).

Mettre à jour

Si votre colonne est numérique, il peut être difficile de vous assurer que la contrainte unique en utilisant Coalesce n'entraîne pas de collisions. Dans ce cas, il y a des options. L'un pourrait être d'utiliser un nombre négatif, de mettre les "NULL de substitution" seulement dans la gamme négative, et les "valeurs réelles" seulement dans la gamme positive. Alternativement, le modèle suivant pourrait être utilisé. Dans la table Issue (où IssueID est le PRIMARY KEY), il peut y avoir ou non TicketID, mais s'il y en a un, il doit être unique.

ALTER TABLE dbo.Issue ADD TicketUnique
   AS (CASE WHEN TicketID IS NULL THEN IssueID END);
ALTER TABLE dbo.Issue ADD CONSTRAINT UQ_Issue_Ticket_AllowNull
   UNIQUE (TicketID, TicketUnique);

Si IssueID 1 a le ticket 123, le UNIQUE la contrainte sera sur les valeurs (123, NULL). Si IssueID 2 n'a pas de ticket, il sera activé (NULL, 2). Certains pensent que cette contrainte ne peut être dupliquée pour aucune ligne de la table, et autorise plusieurs valeurs NULL.


24
2017-11-12 05:04



Pour les personnes qui utilisent Microsoft SQL Server Manager et souhaitez créer un index Unique mais nullable, vous pouvez créer votre index unique comme vous le feriez normalement dans les propriétés de votre index pour votre nouvel index, sélectionnez "Filtre" dans le panneau de gauche, puis entrez votre filtre (qui est votre clause where) . Il devrait lire quelque chose comme ceci:

([YourColumnName] IS NOT NULL)

Cela fonctionne avec MSSQL 2012


13
2017-09-20 17:20



Lorsque j'ai appliqué l'index unique ci-dessous:

CREATE UNIQUE NONCLUSTERED INDEX idx_badgeid_notnull
ON employee(badgeid)
WHERE badgeid IS NOT NULL;

chaque mise à jour et insertion non null a échoué avec l'erreur ci-dessous:

UPDATE a échoué car les options SET suivantes ont des paramètres incorrects: 'ARITHABORT'.

J'ai trouvé ça sur MSDN

SET ARITHABORT doit être activé lorsque vous créez ou modifiez des index sur des colonnes calculées ou des vues indexées. Si SET ARITHABORT est désactivé, les instructions CREATE, UPDATE, INSERT et DELETE sur les tables contenant des index sur des colonnes calculées ou des vues indexées échoueront.

Donc, pour que cela fonctionne correctement, je l'ai fait

Clic droit sur [Base de données] -> Propriétés -> Options -> Autre   Options -> Misscours -> Arithmetic Abort Activé -> true

Je crois qu'il est possible de définir cette option dans le code en utilisant

ALTER DATABASE "DBNAME" SET ARITHABORT ON

mais je n'ai pas testé cela


8
2018-02-06 12:13



Créez une vue qui sélectionne uniquement les nonNULL colonnes et créer le UNIQUE INDEX sur la vue:

CREATE VIEW myview
AS
SELECT  *
FROM    mytable
WHERE   mycolumn IS NOT NULL

CREATE UNIQUE INDEX ux_myview_mycolumn ON myview (mycolumn)

Notez que vous devrez effectuer INSERT'le sable UPDATEest sur la vue au lieu de la table.

Vous pouvez le faire avec un INSTEAD OF déclencheur:

CREATE TRIGGER trg_mytable_insert ON mytable
INSTEAD OF INSERT
AS
BEGIN
        INSERT
        INTO    myview
        SELECT  *
        FROM    inserted
END

6
2018-04-20 10:19



Il est possible de créer une contrainte unique sur une vue indexée en cluster

Vous pouvez créer la vue comme ceci:

CREATE VIEW dbo.VIEW_OfYourTable WITH SCHEMABINDING AS
SELECT YourUniqueColumnWithNullValues FROM dbo.YourTable
WHERE YourUniqueColumnWithNullValues IS NOT NULL;

et la contrainte unique comme ceci:

CREATE UNIQUE CLUSTERED INDEX UIX_VIEW_OFYOURTABLE 
  ON dbo.VIEW_OfYourTable(YourUniqueColumnWithNullValues)

4
2018-04-20 10:21



Cela peut aussi être fait dans le concepteur

Faites un clic droit sur l'index> Propriétés pour avoir cette fenêtre

capture


4
2017-07-18 12:47



Peut-être envisager un "INSTEAD OF"déclenchez et faites vous-même la vérification? Avec un index non clusterisé (non unique) sur la colonne pour activer la recherche.


2
2018-04-20 10:39



Comme indiqué précédemment, SQL Server n'implémente pas la norme ANSI lorsqu'il s'agit de UNIQUE CONSTRAINT. Il y a un billet sur Microsoft Connect pour cela depuis 2007. Comme suggéré ici et ici les meilleures options à ce jour sont d'utiliser un index filtré comme indiqué dans une autre réponse ou une colonne calculée, par exemple:

CREATE TABLE [Orders] (
  [OrderId] INT IDENTITY(1,1) NOT NULL,
  [TrackingId] varchar(11) NULL,
  ...
  [ComputedUniqueTrackingId] AS (
      CASE WHEN [TrackingId] IS NULL
      THEN '#' + cast([OrderId] as varchar(12))
      ELSE [TrackingId_Unique] END
  ),
  CONSTRAINT [UQ_TrackingId] UNIQUE ([ComputedUniqueTrackingId])
)

1
2018-02-26 14:18



Vous pouvez créer un déclencheur INSTEAD OF pour vérifier des conditions spécifiques et des erreurs si elles sont satisfaites. La création d'un index peut être coûteuse sur des tables plus grandes.

Voici un exemple:

CREATE TRIGGER PONY.trg_pony_unique_name ON PONY.tbl_pony
 INSTEAD OF INSERT, UPDATE
 AS
BEGIN
 IF EXISTS(
    SELECT TOP (1) 1 
    FROM inserted i
    GROUP BY i.pony_name
    HAVING COUNT(1) > 1     
    ) 
     OR EXISTS(
    SELECT TOP (1) 1 
    FROM PONY.tbl_pony t
    INNER JOIN inserted i
    ON i.pony_name = t.pony_name
    )
    THROW 911911, 'A pony must have a name as unique as s/he is. --PAS', 16;
 ELSE
    INSERT INTO PONY.tbl_pony (pony_name, stable_id, pet_human_id)
    SELECT pony_name, stable_id, pet_human_id
    FROM inserted
 END

0
2018-05-04 20:30