我想在一个列上有一个唯一的约束,我将用guid填充这个列。但是,我的数据包含这些列的空值。如何创建允许多个空值的约束?

下面是一个示例场景。考虑这个模式:

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

然后看看我想要实现的代码:

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

最后一个语句失败,并返回一条消息:

违反唯一键约束'UQ_People_LibraryCardId'。不能在对象'dbo.People'中插入重复的键。

我如何改变我的模式和/或唯一性约束,使它允许多个NULL值,同时仍然检查对实际数据的唯一性?


当前回答

您可以创建一个INSTEAD OF触发器来检查特定的条件和错误,如果满足这些条件的话。在较大的表上创建索引的成本可能很高。

这里有一个例子:

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

其他回答

您可以创建一个INSTEAD OF触发器来检查特定的条件和错误,如果满足这些条件的话。在较大的表上创建索引的成本可能很高。

这里有一个例子:

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

你不能用UNIQUE约束这样做,但你可以在触发器中这样做。

    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

根据我的经验——如果你认为一个列需要允许null,但也需要为它们存在的值设置UNIQUE,那么你可能对数据建模不正确。这通常表明您在同一个表中创建了一个单独的子实体作为一个不同的实体。将这个实体放在第二个表中可能更有意义。

在提供的例子中,我将把LibraryCardId放在一个单独的LibraryCards表中,并在People表中添加一个唯一的非空外键:

CREATE TABLE People (
  Id INT CONSTRAINT PK_MyTable PRIMARY KEY IDENTITY,
  Name NVARCHAR(250) NOT NULL,
)
CREATE TABLE LibraryCards (    
  LibraryCardId UNIQUEIDENTIFIER CONSTRAINT PK_LibraryCards PRIMARY KEY,
  PersonId INT NOT NULL
  CONSTRAINT UQ_LibraryCardId_PersonId UNIQUE (PersonId),
  FOREIGN KEY (PersonId) REFERENCES People(id)
)

这样,您就不需要为列既唯一又可为空而烦恼了。如果一个人没有借书证,他就不会在借书证表上有记录。此外,如果有关于借书证的其他属性(可能是Expiration Date或其他属性),那么现在就有了一个放置这些字段的逻辑位置。

当我应用下面的唯一索引时:

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

每次非空更新和插入失败,错误如下:

更新失败,因为以下SET选项有不正确的设置:'ARITHABORT'。

我在MSDN上找到的

当您在计算列或索引视图上创建或更改索引时,SET ARITHABORT必须为ON。如果SET ARITHABORT为OFF,则在计算列或索引视图上有索引的表上的CREATE、UPDATE、INSERT和DELETE语句将失败。

为了让它正确工作,我这样做了

右键单击[数据库]——>属性——>选项——>其他 选项——> miscellaneous——>算术中止Enabled——>true

我相信可以在代码中使用

ALTER DATABASE "DBNAME" SET ARITHABORT ON

但我还没有测试过

SQL Server 2008 +

您可以使用WHERE子句创建接受多个null的唯一索引。请看下面的答案。

在SQL Server 2008之前

不能创建UNIQUE约束并允许为null。需要设置默认值NEWID()。

在创建UNIQUE约束之前,将现有值更新为NEWID(),其中为NULL。