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

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

当前回答

您可以创建一个INSTEAD OF触发器来检查特定的条件和错误，如果满足这些条件的话。在较大的表上创建索引的成本可能很高。

这里有一个例子:

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

2018-05-04 20:30:10

其他回答

你要找的确实是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 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

如前所述，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

对于使用Microsoft SQL Server管理器并想要创建唯一但可空索引的人，您可以创建您的唯一索引，然后在您的新索引的索引属性中为您的新索引创建索引，从左侧面板选择“过滤器”，然后输入您的过滤器(这是您的where子句)。它应该是这样的:

([YourColumnName] IS NOT NULL)

这适用于MSSQL 2012

2013-09-20 17:20:00

也许可以考虑使用“代替”触发器，然后自己检查?使用列上的非聚集(非唯一)索引来启用查找。

2009-04-20 10:39:10

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

推荐文章

最新文章

标签