我希望在一个列上有一个唯一约束,我将使用GUID填充该列 . 但是,我的数据包含此列的空值 . 如何创建允许多个空值的约束?
这是一个example scenario . 考虑这个架构:
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
值,同时仍检查实际数据的唯一性?
14 回答
SQL Server 2008 And Up
只需过滤一个唯一索引:
In Lower Versions, A Materialized View Is Still Not Required
对于SQL Server 2005及更早版本,您可以在没有视图的情况下执行此操作 . 我刚刚添加了一个独特的约束,就像你要求我的一张 table 一样 . 鉴于我想要列
SamAccountName
中的唯一性,但我想允许多个NULL,我使用了物化列而不是物化视图:您只需在计算列中放入一些内容,当实际所需的唯一列为NULL时,该列将在整个表中保证唯一 . 在这种情况下,
PartyID
是一个标识列,数字将永远不会匹配SamAccountName
,所以它对我有用 . 您可以尝试自己的方法 - 确保您了解数据的域,以便不可能与实际数据交叉 . 这可能就像预先设置这样的区分字符一样简单:即使有一天
PartyID
变成非数字并且可能与SamAccountName
重合,现在也没关系 .请注意,包含计算列的索引的存在会隐式地使每个表达式结果与表中的其他数据一起保存到磁盘,这会占用额外的磁盘空间 .
请注意,如果您不想要索引,则仍可以通过将关键字
PERSISTED
添加到列表达式定义的末尾,将表达式预先计算到磁盘来节省CPU .在SQL Server 2008及更高版本中,如果可能,请务必使用已过滤的解决方案!
Controversy
请注意,一些数据库专业人员会将此视为"surrogate NULLs"的情况,这肯定存在问题(主要是由于试图确定某些内容是真实值或缺失数据的替代值的问题;数字也可能存在问题非NULL代理值的乘法就像疯了一样) .
但是,我认为这种情况有所不同 . 我正在添加的计算列永远不会用于确定任何内容 . 它没有任何意义,并且没有编码在其他正确定义的列中没有单独找到的信息 . 永远不应该选择或使用它 .
所以,我的故事是这不是代理NULL,并且我实际上想要非NULL值用于除了欺骗
UNIQUE
索引忽略NULL之外的任何目的,我们的用例没有任何正常情况下出现的问题代理NULL创建 .所有这一切,我没有使用索引视图的问题 - 但它带来了一些问题,如使用
SCHEMABINDING
的要求 . 在基表中添加新列很有趣(您至少必须删除索引,然后删除视图或将视图更改为不受模式限制) . 查看完整(长)list of requirements for creating an indexed view in SQL Server (2005)(也是更高版本),(2000) .Update
如果您的列是数字,则可能存在确保使用
Coalesce
的唯一约束不会导致冲突的挑战 . 在这种情况下,有一些选择 . 一个可能是使用负数,将"surrogate NULLs"仅设置在负范围内,而"real values"仅设置在正范围内 . 或者,可以使用以下模式 . 在表Issue
(其中IssueID
是PRIMARY KEY
)中,可能有也可能没有TicketID
,但如果有,则必须是唯一的 .如果IssueID 1具有票证123,则
UNIQUE
约束将在值(123,NULL)上 . 如果IssueID 2没有票证,则它将打开(NULL,2) . 有些人认为这个约束不能复制到表中的任何行,并且仍然允许多个NULL .它也可以在设计师中完成
右键单击索引> Properties 以获取此窗口
您不能使用
UNIQUE
约束执行此操作,但可以在触发器中执行此操作 .可以在聚簇索引视图上创建唯一约束
您可以像这样创建视图:
和这样的独特约束:
对于正在使用 Microsoft SQL Server Manager 且想要创建Unique但Nullable索引的人,您可以按照通常在新索引的索引属性中创建唯一索引,从左侧面板中选择"Filter",然后输入您的过滤器(即你的where子句) . 它应该是这样的:
这适用于MSSQL2012
当我应用下面的唯一索引时:
每个非null更新和插入失败,错误如下:
我在MSDN找到了这个
所以为了让这个工作正常,我做到了这一点
我相信可以在代码中使用设置此选项
但我没有测试过这个
您正在寻找的确实是ANSI标准SQL:92,SQL:1999和SQL:2003的一部分,即UNIQUE约束必须禁止重复的非NULL值但接受多个NULL值 .
但是,在SQL Server的Microsoft世界中,允许单个NULL,但多个NULL不是......
在 SQL Server 2008 中,您可以基于排除NULL的谓词定义唯一的筛选索引:
在早期版本中,您可以使用带有NOT NULL谓词的VIEWS来强制执行约束 .
您可以创建一个INSTEAD OF触发器来检查特定条件和错误(如果满足) . 在较大的表上创建索引可能成本很高 .
这是一个例子:
SQL Server 2008
您可以使用
WHERE
子句创建一个接受多个NULL的唯一索引 . 见answer below .在SQL Server 2008之前
您不能创建UNIQUE约束并允许NULL . 您需要设置NEWID()的默认值 .
在创建UNIQUE约束之前,将现有值更新为NEWID(),其中为NULL .
也许考虑一个“
INSTEAD OF
”触发器并自己检查?使用列上的非聚集(非唯一)索引来启用查找 .创建一个仅选择非
NULL
列的视图,并在视图上创建UNIQUE INDEX
:请注意,您需要在视图而不是表上执行
INSERT
和UPDATE
.您可以使用
INSTEAD OF
触发器执行此操作:这段代码如果你用textBox创建一个注册表单并使用insert和你的textBox是空的,你点击提交按钮 .
如前所述,SQL Server在
UNIQUE CONSTRAINT
时没有实现ANSI标准 . 自2007年以来,有一个ticket on Microsoft Connect . 正如那里和here所建议的那样,今天最好的选择是使用another answer中所述的过滤索引或计算列,例如: