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

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

当前回答

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

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

为了让它正确工作，我这样做了

右键单击[数据库]——>属性——>选项——>其他选项——> miscellaneous——>算术中止Enabled——>true

我相信可以在代码中使用

ALTER DATABASE "DBNAME" SET ARITHABORT ON

但我还没有测试过

2013-02-06 12:13:07

其他回答

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

CREATE UNIQUE NONCLUSTERED INDEX idx_badgeid_notnull
ON employee(badgeid)
WHERE badgeid IS NOT NULL;

每次非空更新和插入失败，错误如下:

更新失败，因为以下SET选项有不正确的设置:'ARITHABORT'。

我在MSDN上找到的

为了让它正确工作，我这样做了

右键单击[数据库]——>属性——>选项——>其他选项——> miscellaneous——>算术中止Enabled——>true

我相信可以在代码中使用

ALTER DATABASE "DBNAME" SET ARITHABORT ON

但我还没有测试过

2013-02-06 12:13:07

创建一个只选择非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

2009-04-20 10:19:40

SQL Server 2008 +

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

在SQL Server 2008之前

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

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

2009-04-20 10:20:27

如前所述，SQL Server在UNIQUE约束方面没有实现ANSI标准。从2007年开始，微软连接就有这样的罚单。正如这里和这里所建议的，到目前为止，最好的选择是使用另一个答案或计算列中所述的过滤索引，例如:

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

2015-02-26 14:18:17

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

你可以像这样创建视图:

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)

2009-04-20 10:21:13

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

推荐文章

最新文章

标签