如何创建一个也允许空值的唯一约束？

您正在寻找的确实是 ANSI 标准 SQL:92、SQL:1999 和 SQL:2003 的一部分，即 UNIQUE 约束必须不允许重复的非 NULL 值但接受多个 NULL 值。

然而，在 SQL Server 的 Microsoft 世界中，允许使用单个 NULL，但不允许使用多个 NULL...

在 SQL Server 2008 中，您可以基于排除 NULL 的谓词定义唯一过滤索引：

CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
ON YourTable(yourcolumn)
WHERE yourcolumn IS NOT NULL;

在早期版本中，您可以使用带有 NOT NULL 谓词的 VIEWS 来强制执行约束。

---

SQL Server 2008 +

您可以使用 WHERE 子句创建一个接受多个 NULL 的唯一索引。请参阅 answer below。

在 SQL Server 2008 之前

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

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

---

SQL Server 2008 及更高版本

只需过滤一个唯一索引：

CREATE UNIQUE NONCLUSTERED INDEX UQ_Party_SamAccountName
ON dbo.Party(SamAccountName)
WHERE SamAccountName IS NOT NULL;

在较低版本中，仍然不需要物化视图

对于 SQL Server 2005 和更早版本，您可以在没有视图的情况下执行此操作。我刚刚添加了一个独特的约束，就像你要求我的一张桌子一样。鉴于我希望列 SamAccountName 中的唯一性，但我想允许多个 NULL，我使用了物化列而不是物化视图：

ALTER TABLE dbo.Party ADD SamAccountNameUnique
   AS (Coalesce(SamAccountName, Convert(varchar(11), PartyID)))
ALTER TABLE dbo.Party ADD CONSTRAINT UQ_Party_SamAccountName
   UNIQUE (SamAccountNameUnique)

当实际所需的唯一列为 NULL 时，您只需在计算列中放入将保证在整个表中唯一的内容。在这种情况下，PartyID 是一个标识列，数字永远不会匹配任何 SamAccountName，所以它对我有用。您可以尝试自己的方法——确保您了解数据的领域，以免与真实数据相交。这可以像添加这样的微分字符一样简单：

Coalesce('n' + SamAccountName, 'p' + Convert(varchar(11), PartyID))

即使 PartyID 有一天变成非数字并且可能与 SamAccountName 重合，现在它也无关紧要了。

请注意，包含计算列的索引的存在隐式导致每个表达式结果与表中的其他数据一起保存到磁盘，这会占用额外的磁盘空间。

请注意，如果您不需要索引，您仍然可以通过将关键字 PERSISTED 添加到列表达式定义的末尾来将表达式预先计算到磁盘来节省 CPU。

在 SQL Server 2008 及更高版本中，如果可能，请务必使用过滤解决方案！

争议

请注意，一些数据库专业人士会将此视为“代理 NULL”的情况，这肯定存在问题（主要是由于试图确定某事物何时是真实值或缺失数据的代理值的问题；也可能存在问题随着非 NULL 代理值的数量疯狂地增加）。

但是，我相信这个案例是不同的。我添加的计算列将永远不会用于确定任何内容。它本身没有任何意义，并且不编码在其他正确定义的列中尚未单独找到的信息。永远不应选择或使用它。

所以，我的故事是，这不是一个代理 NULL，我坚持它！由于我们实际上不希望非 NULL 值用于欺骗 UNIQUE 索引以忽略 NULL 之外的任何目的，因此我们的用例没有正常代理 NULL 创建所出现的问题。

综上所述，我对使用索引视图没有任何问题，但它带来了一些问题，例如使用 SCHEMABINDING 的要求。向您的基表添加新列很有趣（您至少必须删除索引，然后删除视图或将视图更改为不受模式绑定）。请参阅完整（长）list of requirements for creating an indexed view in SQL Server (2005)（以及更高版本）(2000)。

更新

如果您的列是数字列，则可能存在确保使用 Coalesce 的唯一约束不会导致冲突的挑战。在这种情况下，有一些选择。一种可能是使用负数，将“代理 NULL”仅放在负范围内，而将“实际值”仅放在正范围内。或者，可以使用以下模式。在表 Issue（其中 IssueID 是 PRIMARY KEY）中，可能有也可能没有 TicketID，但如果有，它必须是唯一的。

ALTER TABLE dbo.Issue ADD TicketUnique
   AS (CASE WHEN TicketID IS NULL THEN IssueID END);
ALTER TABLE dbo.Issue ADD CONSTRAINT UQ_Issue_Ticket_AllowNull
   UNIQUE (TicketID, TicketUnique);

如果 IssueID 1 具有票证 123，则 UNIQUE 约束将在值 (123, NULL) 上。如果 IssueID 2 没有票证，它将为 (NULL, 2)。有些想法会表明，这个约束不能对表中的任何行重复，并且仍然允许多个 NULL。

---

对于使用 Microsoft SQL Server Manager 并希望创建唯一但可为空的索引的人，您可以像往常一样创建唯一索引，然后在新索引的索引属性中，从左侧面板中选择“过滤器”，然后输入您的过滤器（这是您的 where 子句）。它应该是这样的：

([YourColumnName] IS NOT NULL)

这适用于 MSSQL 2012

---

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

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

所以为了让它正常工作，我这样做了

右键单击[Database]-->Properties-->Options-->Other Options-->Misscellaneous-->Arithmetic Abort Enabled-->true

我相信可以在代码中使用

ALTER DATABASE "DBNAME" SET ARITHABORT ON

但我没有测试过这个

---

也可以在设计器中完成

右键单击索引>属性以获取此窗口

https://i.stack.imgur.com/nCplb.png

---

创建仅选择非 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

---

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

您可以像这样创建视图：

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)

---

以我的经验 - 如果您认为列需要允许 NULL，但对于它们存在的值也需要是唯一的，那么您可能错误地对数据建模。这通常表明您在同一个表中创建一个单独的子实体作为不同的实体。将此实体放在第二个表中可能更有意义。

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

这样，您就不必为既独特又可为空的列烦恼。如果一个人没有借书证，他们就不会在借书证表中记录。此外，如果有关于图书卡的其他属性（可能是到期日期或其他内容），您现在有一个放置这些字段的合理位置。

---

也许考虑一个“INSTEAD OF”触发器并自己进行检查？在列上使用非聚集（非唯一）索引来启用查找。

---

如前所述，对于 UNIQUE CONSTRAINT，SQL Server 没有实现 ANSI 标准。自 2007 年以来有一个 ticket on Microsoft Connect。正如那里所建议的和 here，迄今为止最好的选择是使用 another answer 中所述的过滤索引或计算列，例如：

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

---

您可以创建一个 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

---

您不能使用 UNIQUE 约束来执行此操作，但您可以在触发器中执行此操作。

    CREATE TRIGGER [dbo].[OnInsertMyTableTrigger]
   ON  [dbo].[MyTable]
   INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;

    DECLARE @Column1 INT;
    DECLARE @Column2 INT; -- allow nulls on this column

    SELECT @Column1=Column1, @Column2=Column2 FROM inserted;

    -- Check if an existing record already exists, if not allow the insert.
    IF NOT EXISTS(SELECT * FROM dbo.MyTable WHERE Column1=@Column1 AND Column2=@Column2 @Column2 IS NOT NULL)
    BEGIN
        INSERT INTO dbo.MyTable (Column1, Column2)
            SELECT @Column2, @Column2;
    END
    ELSE
    BEGIN
        RAISERROR('The unique constraint applies on Column1 %d, AND Column2 %d, unless Column2 is NULL.', 16, 1, @Column1, @Column2);
        ROLLBACK TRANSACTION;   
    END

END

---

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

---

如果您使用 textBox 制作注册表单并使用 insert 并且您的 textBox 为空并且您单击提交按钮，则此代码。

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

---