Comment créer une contrainte unique qui permet aussi des nulls?

je veux avoir une contrainte unique sur une colonne que je vais peupler de GUIDs. Cependant, mes données contiennent des valeurs nulles pour ces colonnes. Comment créer la contrainte qui permet plusieurs valeurs nulles?

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

alors voyez ce code pour ce que j'essaie d'atteindre:

-- 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 clé UNIQUE "UQ_People_LibraryCardId". Ne peut pas insérer la clé dupliquée dans le dbo de l'objet.Les gens".

Comment puis-je modifier mon schéma et/ou la contrainte d'unicité pour qu'elle autorise des valeurs multiples NULL , tout en vérifiant l'unicité des données réelles?

510
demandé sur Jeroen 2009-04-20 14:12:55

14 réponses

SQL Server 2008 +

vous pouvez créer un index unique qui accepte plusieurs NULLs avec une clause WHERE . Voir la réponse ci-dessous .

avant SQL Server 2008

vous ne pouvez pas créer une contrainte UNIQUE et permettre NULLs. Vous devez définir une valeur par défaut de NEWID ().

mettre à Jour les valeurs existantes pour NEWID() où NULL avant de créer une contrainte UNIQUE.

116
répondu Jose Basilio 2017-05-23 12:10:44

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 nulles dupliquées mais accepter des valeurs nulles multiples.

dans le monde Microsoft de SQL Server cependant, un seul NULL est autorisé mais les null multiples ne le sont pas...

dans SQL Server 2008 , vous pouvez définir un index filtré unique basé sur un prédicat qui exclut NULLs:

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

dans les versions précédentes, vous pouvez recourir à des vues avec un prédicat non nul pour appliquer la contrainte.

1139
répondu Vincent Buck 2009-04-20 10:31:34

SQL Server 2008 And Up

il suffit de filtrer un indice 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 Nécessaire

pour SQL Server 2005 et avant, vous pouvez le faire sans une vue. Je viens d'ajouter une contrainte unique comme vous le demandez à l'une de mes tables. Étant donné que je veux unicité dans la colonne SamAccountName , mais je veux permettre NULLs multiples, 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 avez simplement à mettre quelque chose dans la colonne calculée qui sera garantie unique à travers la table entière quand la colonne désirée réelle est nulle. Dans ce cas, PartyID est une colonne d'identité et être numérique ne correspondra jamais à aucun SamAccountName , donc ça a fonctionné pour moi. Vous pouvez essayer votre propre méthode, assurez-vous de comprendre le domaine de vos données de sorte qu'il n'y a pas de possibilité de croisement avec des données réelles. Cela pourrait être aussi simple que de préparer un caractère différentiateur comme celui-ci:

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

même si PartyID est devenu non-numérique un jour et pourrait coïncider avec un SamAccountName , maintenant il ne sera pas important.

notez que la présence d'un index incluant la colonne calculée provoque implicitement que chaque résultat d'expression soit sauvegardé sur le disque avec les autres données dans la table, ce qui ne prend disque supplémentaire espace.

notez que si vous ne voulez pas d'index, vous pouvez tout de même sauvegarder CPU en faisant précalculer l'expression sur disque en ajoutant le mot-clé PERSISTED à la fin de la définition de l'expression de colonne.

dans SQL Server 2008 et plus, certainement utiliser la solution filtrée à la place si vous le pouvez!

controverse

veuillez noter que certains professionnels des bases de données va voir cela comme un cas de "mère porteuse null", qui ont certainement des problèmes (principalement en raison de problèmes autour d'essayer de déterminer quand quelque chose est une valeur réelle ou une valeur de remplacement pour les données manquantes ; il peut également y avoir des problèmes avec le nombre de valeurs de mère porteuse non-nul se multipliant comme un fou).

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

donc, mon histoire est que ce n'est pas une mère porteuse nulle, et je m'y tiens! Puisque nous ne voulons pas réellement la valeur non-NULL dans un but autre que de tromper l'indice UNIQUE pour ignorer NULLs, notre cas d'utilisation n'a aucun des problèmes qui se posent avec la création normale de NULL de substitution.

tout cela dit, Je n'ai aucun problème à utiliser une vue indexée à la place-mais il apporte quelques problèmes avec elle tels que l'exigence d'utiliser SCHEMABINDING . Amusez-vous à ajouter une nouvelle colonne à votre table de base (vous devrez au minimum supprimer l'index, puis supprimer la vue ou modifier la vue pour ne pas être liée au schéma). Voir la liste complète (longue) des exigences pour la création d'une vue indexée dans SQL Server (2005) (également versions suivantes)), (2000) .

mise à Jour

si votre colonne est numérique, il peut être difficile de s'assurer que la contrainte unique utilisant Coalesce n'entraîne pas de collisions. Dans ce cas, il y a quelques options. Il pourrait s'agir d'utiliser un nombre négatif, de ne mettre les "valeurs nulles de remplacement" que dans l'intervalle négatif, et les "valeurs réelles" que dans l'intervalle positif. Alternativement, le schéma suivant peut être utilisé. Dans le tableau Issue (où IssueID est le PRIMARY KEY ), il peut y avoir ou non un 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, la contrainte UNIQUE sera sur les valeurs (123, NULL). Si IssueID 2 n'a pas de ticket, il sera activé (NULL, 2). Une certaine réflexion montrera que cette contrainte ne peut pas être dupliquée pour n'importe quelle ligne dans le tableau, et permet toujours des NULLs multiples.

24
répondu ErikE 2017-11-15 20:19:31

pour les personnes qui utilisent Microsoft SQL Server Manager et qui veulent créer un index unique mais nul, vous pouvez créer votre index unique comme vous le feriez normalement alors dans vos propriétés D'Index pour votre nouvel index, sélectionnez" Filter " à partir du 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

14
répondu Howard 2013-09-20 17:20:00

quand j'ai appliqué l'indice unique ci-dessous:

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

chaque mise à jour non nulle et insert échoué avec l'erreur ci-dessous:

La mise à jour

a échoué parce que les options suivantes ont des réglages incorrects: 'ARITHABORT'.

j'ai trouvé ceci sur MSDN

ensemble ARITHMABORT doit être sur lorsque vous créez ou changez les index sur colonnes calculées ou vues indexées. Si SET ARITHABORT est désactivé, Créer, mettre à jour, Insérer et supprimer des énoncés sur les tables avec des index sur les colonnes calculées ou les vues indexées échouera.

donc pour que ça marche correctement j'ai fait ça

clic droit [base de données]-- > propriétés-- > Options-- > autres Options-- > diffus -- > abandon arithmétique activé -- > vrai

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é ce

9
répondu Michael Taylor 2013-02-07 11:07:39

créer une vue qui sélectionne seulement les colonnes non- NULL 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 's et UPDATE 's 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
répondu Quassnoi 2009-04-20 10:25:28

il est possible de créer une contrainte unique sur une vue indexée groupée

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 celle-ci:

CREATE UNIQUE CLUSTERED INDEX UIX_VIEW_OFYOURTABLE 
  ON dbo.VIEW_OfYourTable(YourUniqueColumnWithNullValues)
4
répondu Lieven Keersmaekers 2009-04-20 10:30:19

Il peut être fait dans le concepteur ainsi

clic droit sur l'Index > propriétés pour obtenir cette fenêtre

capture

4
répondu Yonatan Tuchinsky 2017-07-18 12:47:24

peut-être considérer un INSTEAD OF " déclencheur et faire le contrôle vous-même? Avec un index non-clustered (non-unique) sur la colonne pour activer la recherche.

2
répondu Marc Gravell 2009-04-20 10:39:10

comme indiqué précédemment, SQL Server n'implémente pas la norme ANSI en ce qui concerne UNIQUE CONSTRAINT . Il y a un ticket sur Microsoft Connect pour cela depuis 2007. Comme suggéré ici et ici les meilleures options à partir d'aujourd'hui 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
répondu Baris Akar 2017-05-23 12:34:45

vous pouvez créer un déclencheur au lieu de vérifier les conditions spécifiques et l'erreur si elles sont remplies. 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
répondu Paul 2018-05-04 20:56:27

vous ne pouvez pas faire cela avec une contrainte UNIQUE , mais vous pouvez le faire dans un déclencheur.

    CREATE TRIGGER [dbo].[OnInsertMyTableTrigger]
   ON  [dbo].[MyTable]
   INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @Column1 INT;
    DECLARE @Column2 INT; -- allow nulls on this column

    SELECT @Column1=Column1, @Column2=Column2 FROM inserted;

    -- Check if an existing record already exists, if not allow the insert.
    IF NOT EXISTS(SELECT * FROM dbo.MyTable WHERE Column1=@Column1 AND Column2=@Column2 @Column2 IS NOT NULL)
    BEGIN
        INSERT INTO dbo.MyTable (Column1, Column2)
            SELECT @Column2, @Column2;
    END
    ELSE
    BEGIN
        RAISERROR('The unique constraint applies on Column1 %d, AND Column2 %d, unless Column2 is NULL.', 16, 1, @Column1, @Column2);
        ROLLBACK TRANSACTION;   
    END

END
-1
répondu Michael Brown 2015-01-01 05:11:16
CREATE UNIQUE NONCLUSTERED INDEX [UIX_COLUMN_NAME]
ON [dbo].[Employee]([Username] ASC) WHERE ([Username] IS NOT NULL) 
WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, 
DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF, 
MAXDOP = 0) ON [PRIMARY];
-1
répondu user5536124 2015-11-07 08:20:10

ce code si u faire un formulaire d'enregistrement avec textBox et utiliser insert et ur textBox est vide et u cliquez sur le bouton Soumettre .

CREATE UNIQUE NONCLUSTERED INDEX [IX_tableName_Column]
ON [dbo].[tableName]([columnName] ASC) WHERE [columnName] !=`''`;
-1
répondu Ahmed Soliman Flasha 2015-11-21 16:32:36