我想在一个列上有一个唯一的约束,我将用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值,同时仍然检查对实际数据的唯一性?


当前回答

也许可以考虑使用“代替”触发器,然后自己检查?使用列上的非聚集(非唯一)索引来启用查找。

其他回答

SQL Server 2008 +

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

在SQL Server 2008之前

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

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

你要找的确实是ANSI标准SQL:92, SQL:1999和SQL:2003的一部分,即UNIQUE约束必须禁止重复的非NULL值,但接受多个NULL值。

然而,在微软的SQL Server世界中,一个NULL是允许的,但多个NULL是不允许的…

在SQL Server 2008中,您可以基于排除null的谓词定义一个唯一的过滤索引:

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

在早期版本中,您可以使用带有NOT NULL谓词的VIEWS来强制执行约束。

这段代码如果你使一个注册表单与文本框和使用插入和你的文本框是空的,你点击提交按钮。

CREATE UNIQUE NONCLUSTERED INDEX [IX_tableName_Column]
ON [dbo].[tableName]([columnName] ASC) WHERE [columnName] !=`''`;

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

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

但我还没有测试过

也许可以考虑使用“代替”触发器,然后自己检查?使用列上的非聚集(非唯一)索引来启用查找。