我如何创建一个唯一的约束，也允许空?

我想在一个列上有一个唯一的约束，我将用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值，同时仍然检查对实际数据的唯一性?

当前回答

SQL Server 2008 +

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

在SQL Server 2008之前

不能创建UNIQUE约束并允许为null。需要设置默认值NEWID()。

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

2009-04-20 10:20:27

其他回答

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

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

这样，您就不需要为列既唯一又可为空而烦恼了。如果一个人没有借书证，他就不会在借书证表上有记录。此外，如果有关于借书证的其他属性(可能是Expiration Date或其他属性)，那么现在就有了一个放置这些字段的逻辑位置。

2021-03-25 14:54:47

SQL Server 2008 +

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

在SQL Server 2008之前

不能创建UNIQUE约束并允许为null。需要设置默认值NEWID()。

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

2009-04-20 10:20:27

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

然而，在微软的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来强制执行约束。

2009-04-20 10:31:34

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];

2015-11-07 07:22:50

这段代码如果你使一个注册表单与文本框和使用插入和你的文本框是空的，你点击提交按钮。

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

2015-11-21 16:12:01

我如何创建一个唯一的约束，也允许空?

推荐文章

最新文章

标签