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

您所寻找的确实是ANSI标准SQL:92、SQL:1999和SQL:2003的一部分,即唯一约束必须禁止重复的非空值,但接受多个空值。

但是,在SQL Server的Microsoft世界中,允许单个NULL,但不允许多个NULL。。。

在里面 ,可以基于排除空值的谓词定义唯一的筛选索引:

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

SQL Server 2008+

您可以创建一个唯一的索引,该索引使用 WHERE 条款见 answer below .

不能创建唯一约束并允许为空。您需要设置NEWID()的默认值。

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

SQL Server 2008及以上版本

只需筛选唯一索引:

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

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 是标识列,且为数字将永远不会与任何 ,所以它对我有效。您可以尝试自己的方法确保您了解数据的域,这样就不可能与真实数据相交。这可能很简单,只需预先设置一个区分符,如下所示:

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

即使 PartyID 有一天会变成非数字的,并且可能与 SamAccountName ,现在没关系了。

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

在SQL Server 2008及更高版本中,如果可能的话,一定要使用过滤后的解决方案!

争议

请注意,一些数据库专业人士会将此视为“代理null”的情况,这肯定会有问题(主要是由于试图确定某个内容何时为空的问题) 缺少数据的代理项值 ; 非空代理项值的数量疯狂地相乘也可能会出现问题)。

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

所以,我的故事是,这不是一个代理无效,我坚持它!因为我们实际上不希望非NULL值用于任何目的,除了欺骗 UNIQUE 为了忽略空值,我们的用例没有任何与正常代理空值创建相关的问题。

尽管如此,我不反对使用索引视图,但它带来了一些问题,比如使用 SCHEMABINDING . 在基表中添加一个新列(您至少需要删除索引,然后删除视图或将视图更改为不受模式绑定)会很有趣。见全文(长) list of requirements for creating an indexed view in SQL Server (2005) (也是最新版本), (2000)

使现代化

如果您的列是数字列,则可能存在确保使用 Coalesce 不会导致碰撞。在这种情况下,有一些选择。一种可能是使用负数,将“代理项空值”仅放在负数范围内,“实数值”仅放在正数范围内。或者,可以使用以下模式。在桌子上 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);

唯一的

Microsoft SQL Server管理器

([YourColumnName] IS NOT NULL)

这适用于MSSQL 2012

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

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

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

我发现这个 MSDN

在计算列或索引视图上创建或更改索引时,SET ARITHABORT必须启用。如果SET ARITHABORT处于禁用状态,则在计算列或索引视图上具有索引的表上创建、更新、插入和删除语句将失败。

选项-->错误的-->算术中止已启用-->符合事实的

我相信在代码中使用

ALTER DATABASE "DBNAME" SET ARITHABORT ON

但我还没有测试过这个

性质 要得到这个窗口

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)

INSTEAD OF “使用列上的非聚集(非唯一)索引触发并自己进行检查”,以启用查找。

如前所述,SQL Server在处理问题时没有实现ANSI标准 UNIQUE CONSTRAINT . 有一个 ticket on Microsoft Connect 从2007年开始。正如在那里和 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])
)

您可以创建一个 而不是

下面是一个例子:

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

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

这样,您就不必为列的唯一性和可空性而烦恼了。如果一个人没有借书证,他们就不会在借书证表中有记录。此外,如果有关于图书卡的附加属性(可能是到期日期或其他),那么您现在就有了放置这些字段的逻辑位置。

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

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