怎样创建一个允许空值的唯一约束的约束条件？

16 浏览
0 Comments

怎样创建一个允许空值的唯一约束的约束条件？

我想在一个我要填充GUID的列上有唯一约束。然而，我的数据对于这些列包含了null值。我如何创建允许多个null值的约束？

这里有一个例子场景。考虑这个架构：

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

最终的语句失败并输出以下信息：

Violation of UNIQUE KEY constraint \'UQ_People_LibraryCardId\'. Cannot insert duplicate key in object \'dbo.People\'.

我应该如何更改我的架构和/或唯一性约束，以允许多个NULL值，同时仍然在实际数据上检查唯一性？

admin 更改状态以发布 2023年5月23日
0

2 答案

0 Comments

SQL Server 2008及以上版本

您可以创建一个带有WHERE子句的唯一索引，可以接受多个NULL。请参见下面的答案。

SQL Server 2008之前的版本

您不能创建一个允许NULL的唯一约束。您需要设定一个默认值为NEWID()。

在创建唯一约束之前，请将现有的NULL值更新为NEWID()。

0
0 Comments

您正在寻找的是ANSI标准SQL:92、SQL:1999和SQL:2003的一部分，即唯一约束必须禁止重复的非空值，但接受多个空值。

然而，在Microsoft SQL Server的世界中，允许一个空值，但不允许多个空值...

在SQL Server 2008中，您可以基于排除NULL的谓词定义唯一过滤索引：

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

在早期版本中，您可以利用带有NOT NULL谓词的视图来强制执行约束。

0