我如何创建一个也允许空值的唯一约束？
我想对我将用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);
最后的声明失败并带有一条消息：
违反UNIQUE KEY约束'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中 ，您可以根据排除空值的谓词定义唯一的过滤索引：
CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;
在早期版本中，您可以使用NOT NULL谓词来执行VIEWS以强制约束。
SQL Server 2008和以上
只需过滤一个独特的索引：
CREATE UNIQUE NONCLUSTERED INDEX UQ_Party_SamAccountName
ON dbo.Party(SamAccountName)
WHERE SamAccountName IS NOT NULL;
在较低版本中，物化视图仍然不是必需的
对于SQL Server 2005和更早版本，您可以在没有视图的情况下执行此操作。 我只是添加了一个独特的约束，就像你要求我的一张桌子一样。 鉴于我希望列SamAccountName唯一性，但我想允许多个NULL，我使用了物化列而不是物化视图：
ALTER TABLE dbo.Party ADD SamAccountNameUnique
AS (Coalesce(SamAccountName, Convert(varchar(11), PartyID)))
ALTER TABLE dbo.Party ADD CONSTRAINT UQ_Party_SamAccountName
UNIQUE (SamAccountNameUnique)
当实际需要的唯一列为NULL时，您只需在计算列中添加一些在整个表中保证唯一的内容。 在这种情况下， PartyID是一个标识列，而数字将永远不会匹配任何SamAccountName ，所以它对我PartyID 。 您可以尝试自己的方法 - 确保您了解数据的域，以便不会与真实数据相交。 这可以像预先考虑这样的区分字符一样简单：
Coalesce('n' + SamAccountName, 'p' + Convert(varchar(11), PartyID))
即使PartyID在某一天变为非数字，并且可能与SamAccountName一致，现在也无关紧要。
请注意，包含计算列的索引隐含地将每个表达式结果与表中的其他数据一起保存到磁盘，这会占用额外的磁盘空间。
请注意，如果您不需要索引，您仍然可以通过将关键字PERSISTED添加到列表达式定义的末尾来预先计算表达式来保存CPU。
在SQL Server 2008及更高版本中，如果可能的话，绝对使用过滤的解决方案！
争议
请注意，有些数据库专业人员会将此视为“代理空值”的情况，这肯定会有问题（主要是由于围绕尝试确定何时是真实值或丢失数据的替代值时出现问题;还可能存在问题非空替代值的数量像疯狂一样倍增）。
不过，我相信这种情况是不同的。 我添加的计算列绝不会用于确定任何内容。 它本身没有意义，并且不编码在其他已正确定义的列中未分别找到的信息。 它不应该被选中或使用。
所以，我的故事是，这不是代理NULL，我坚持！ 由于我们实际上并不希望将非空值用于除欺骗UNIQUE索引以忽略空值之外的任何目的，所以我们的用例没有创建正常的代理空值时出现的问题。
所有这一切，我没有问题，而是使用索引视图 - 但它带来了一些问题，如需要使用SCHEMABINDING 。 在您的基本表中添加一个新的列会很有趣（至少您必须删除索引，然后删除视图或更改视图以避免模式绑定）。 查看在SQL Server（2005）（也是更高版本）（2000）中创建索引视图的完整（长）要求列表。
更新
如果您的列是数字，那么确保使用Coalesce的唯一约束不会导致冲突可能会遇到一些挑战。 在这种情况下，有一些选择。 一种可能是使用负数，将“替代空值”仅放入负值范围，而“真实值”仅放入正值范围。 或者，可以使用以下模式。 在表Issue （其中IssueID是PRIMARY KEY ）中，可能有或没有TicketID ，但是如果有TicketID ，则它必须是唯一的。
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);
如果IssueID 1具有票证123，则UNIQUE约束将位于值（123，NULL）上。 如果IssueID 2没有票证，它将会打开（NULL，2）。 有些想法会显示该约束不能重复表中的任何行，并仍允许多个NULL。
上一篇: How do I create a unique constraint that also allows nulls?