如何创建也允许空值的唯一约束？

我想在要用 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 tsql

答案

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

但是，在 Microsoft 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 来强制执行约束。

SQL Server 2008 以上版本

WHERE子句创建一个接受多个 NULL 的唯一索引。请参阅下面的答案。

在 SQL Server 2008 之前

您不能创建 UNIQUE 约束并允许 NULL。您需要设置默认值 NEWID（）。

在创建 UNIQUE 约束之前，将现有值更新为 NEWID（），其中 NULL。

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匹配，因此它对我有用。您可以尝试使用自己的方法 - 确保您了解数据的范围，以免与真实数据相交。这可以像在前面加上一个区分字符一样简单：

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

即使PartyID某一天变为非数字，并且可能与SamAccountName一致，但现在不再重要。

请注意，包含计算列的索引将隐式导致每个表达式结果与表中的其他数据一起保存到磁盘，这确实占用了额外的磁盘空间。

请注意，如果您不希望使用索引，则仍可以通过在列表达式定义的末尾PERSISTED

在 SQL Server 2008 及更高版本中，如果可以的话，绝对可以使用过滤后的解决方案！

争议

请注意，某些数据库专业人员会将其视为 “代理 NULL” 的情况，它们肯定有问题（主要是由于围绕尝试确定某物何时是真实值或丢失数据的替代值而引起的问题；也可能存在问题与非 NULL 替代值的数量相乘就疯狂了）。

但是，我相信这种情况是不同的。我要添加的计算列将永远不会用于确定任何内容。它本身没有任何意义，也不会编码在其他正确定义的列中尚未单独找到的信息。永远不要选择或使用它。

因此，我的故事是这不是替代 NULL，我坚持使用！ UNIQUE索引以忽略 NULL 之外，我们实际上并不想出于任何目的使用非 NULL 值，因此我们的用例不存在正常的替代 NULL 创建所引起的问题。

综上所述，我使用索引视图没有问题，但是它带来了一些问题，例如使用SCHEMABINDING的要求。祝您在基表中添加新列（您至少必须删除索引，然后删除视图或更改视图以使其不受模式约束）很有趣。请参阅完整（较长）列表，以在 SQL Server（2005）（以及更高版本）（2000）中创建索引视图。

更新

如果您的列为数字，则可能存在确保使用Coalesce的唯一约束不会导致冲突的挑战。在这种情况下，有一些选择。一种可能是使用负数，将 “代理 NULL” 仅置于负范围内，而将 “实际值” 仅置于正范围内。或者，可以使用以下模式。在表Issue （其中IssueID是PRIMARY KEY ）中，可能存在或可能没有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。