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

我想在将要填充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值，同时仍检查实际数据的唯一性？

317380 次浏览
小开

创建一个仅选择非NULL列的视图，并在视图上创建UNIQUE INDEX：

CREATE VIEW myview
AS
SELECT  *
FROM    mytable
WHERE   mycolumn IS NOT NULL


CREATE UNIQUE INDEX ux_myview_mycolumn ON myview (mycolumn)

请注意，您需要在视图而不是表上执行INSERT和UPDATE。

您可以使用INSTEAD OF触发器执行此操作：

CREATE TRIGGER trg_mytable_insert ON mytable
INSTEAD OF INSERT
AS
BEGIN
INSERT
INTO    myview
SELECT  *
FROM    inserted
END
小开
最佳答案

SQLServer 2008+

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

SQLServer 2008

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

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

小开

可以在聚集索引视图上创建唯一约束

你可以像这样创建视图：

CREATE VIEW dbo.VIEW_OfYourTable WITH SCHEMABINDING AS
SELECT YourUniqueColumnWithNullValues FROM dbo.YourTable
WHERE YourUniqueColumnWithNullValues IS NOT NULL;

以及像这样的唯一约束：

CREATE UNIQUE CLUSTERED INDEX UIX_VIEW_OFYOURTABLE
ON dbo.VIEW_OfYourTable(YourUniqueColumnWithNullValues)
小开

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

然而，在Microsoft的SQLServer世界中，允许单个NULL，但不允许多个NULL。

在SQLServer 2008中，您可以根据排除NULL的谓词定义唯一的过滤索引：

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

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

小开

也许考虑一个“INSTEAD OF”触发器并自己进行检查？在列上使用非聚集（非唯一）索引来启用查找。

小开

SQLServer 2008及以上

只需过滤一个唯一索引：

CREATE UNIQUE NONCLUSTERED INDEX UQ_Party_SamAccountName
ON dbo.Party(SamAccountName)
WHERE SamAccountName IS NOT NULL;

在较低版本中，仍然不需要物化视图

对于SQLServer 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添加到列表达式定义的末尾来使表达式预计算到磁盘来节省CPU。

在SQLServer 2008及更高版本中，如果可能的话，一定要使用过滤解决方案！

争议

请注意，一些数据库专业人员会将此视为“代理NULL”的情况，这肯定会有问题（主要是由于试图确定某物是实际价值还是缺失数据的代理值的问题；也可能存在非NULL代理值数量疯狂相乘的问题）。

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

所以，我的故事是，这不是一个代理NULL，我坚持它！由于我们实际上不希望非NULL值用于任何目的，除了欺骗UNIQUE索引忽略NULL之外，我们的用例没有正常代理NULL创建出现的任何问题。

总而言之，我对使用索引视图没有问题——但它带来了一些问题，例如使用SCHEMABINDING的要求。向基表添加新列很有趣（你至少必须删除索引，然后删除视图或更改视图以不受模式绑定）。请参阅完整（长）在SQLServer（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。

小开

您不能使用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
小开

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

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语句将失败。

为了让它正常工作，我做了这个

右键单击[数据库]-->属性-->选项-->其他 选项-->Misscellaneous-->启用算术中止-->true

我相信可以使用以下代码设置此选项 

ALTER DATABASE "DBNAME" SET ARITHABORT ON

但是我还没有测试过这个

小开

对于使用微软SQL服务器管理器并希望创建唯一但可为空的索引的人，您可以创建您的唯一索引，就像通常在新索引的索引属性中那样，从左侧面板中选择“过滤器”，然后输入您的过滤器（这是您的where子句）。它应该如下所示：

([YourColumnName] IS NOT NULL)

这适用于MSSQL 2012

小开

如前所述，SQLServer在UNIQUE CONSTRAINT方面没有实现ANSI标准。自2007年以来，有一个Microsoft Connect上的票证用于此。正如那里和这里所建议的，今天最好的选择是使用另一个答案中所述的过滤索引或计算列，例如：

CREATE TABLE [Orders] (
[OrderId] INT IDENTITY(1,1) NOT NULL,
[TrackingId] varchar(11) NULL,
...
[ComputedUniqueTrackingId] AS (
CASE WHEN [TrackingId] IS NULL
THEN '#' + cast([OrderId] as varchar(12))
ELSE [TrackingId_Unique] END
),
CONSTRAINT [UQ_TrackingId] UNIQUE ([ComputedUniqueTrackingId])
)
小开 
CREATE UNIQUE NONCLUSTERED INDEX [UIX_COLUMN_NAME]
ON [dbo].[Employee]([Username] ASC) WHERE ([Username] IS NOT NULL)
WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, ONLINE = OFF,
MAXDOP = 0) ON [PRIMARY];
小开

此代码如果您使用文本框制作注册表并使用插入并且您的文本框为空并且您单击提交按钮。

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

它也可以在设计师中完成

右键单击索引>属性以获取此窗口

捕获

小开

您可以创建而不是触发器来检查是否满足特定条件和错误。在较大的表上创建索引的成本可能很高。

这里有一个例子：

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
小开

根据我的经验——如果你认为一列需要允许NULL，但对于存在的值也需要是唯一的，你可能对数据建模不正确。这通常表明你在同一个表中创建了一个单独的子实体作为不同的实体。将这个实体放在第二个表中可能更有意义。

在提供的示例中，我将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)
)

这样，您就不必担心列既是唯一的又是可空的。如果一个人没有借书证，他们在借书证表中就不会有记录。此外，如果借书证有其他属性（可能是到期日期或其他东西），您现在有一个逻辑位置来放置这些字段。


提交答案