如何创建一个也允许空值的唯一约束? [英] How do I create a unique constraint that also allows nulls?
问题描述
我想对要填充 GUID 的列设置唯一约束.但是,我的数据包含此列的空值.如何创建允许多个空值的约束?
I want to have a unique constraint on a column which I am going to populate with GUIDs. However, my data contains null values for this columns. How do I create the constraint that allows multiple null values?
这是一个示例场景.考虑这个模式:
Here's an example scenario. Consider this schema:
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)
)
然后查看此代码以了解我要实现的目标:
Then see this code for what I'm trying to achieve:
-- 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);
最后的语句失败并显示一条消息:
The final statement fails with a message:
违反 UNIQUE KEY 约束UQ_People_LibraryCardId".无法在对象dbo.People"中插入重复键.
Violation of UNIQUE KEY constraint 'UQ_People_LibraryCardId'. Cannot insert duplicate key in object 'dbo.People'.
如何更改架构和/或唯一性约束,使其允许多个 NULL
值,同时仍检查实际数据的唯一性?
How can I change my schema and/or uniqueness constraint so that it allows multiple NULL
values, while still checking for uniqueness on actual data?
推荐答案
SQL Server 2008 +
您可以使用 WHERE
子句创建一个接受多个 NULL 的唯一索引.请参阅下面的答案.
SQL Server 2008 +
You can create a unique index that accept multiple NULLs with a WHERE
clause. See the answer below.
您不能创建 UNIQUE 约束并允许 NULL.您需要设置 NEWID() 的默认值.
You cannot create a UNIQUE constraint and allow NULLs. You need set a default value of NEWID().
在创建 UNIQUE 约束之前将现有值更新为 NEWID() where NULL.
Update the existing values to NEWID() where NULL before creating the UNIQUE constraint.
这篇关于如何创建一个也允许空值的唯一约束?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!