首页 文章

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

提问于
浏览
519

我希望在一个列上有一个唯一约束,我将使用GUID填充该列 . 但是,我的数据包含此列的空值 . 如何创建允许多个空值的约束?

这是一个example scenario . 考虑这个架构:

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

最终语句失败并显示一条消息:

违反UNIQUE KEY约束'UQ_People_LibraryCardId' . 无法在对象'dbo.People'中插入重复键 .

如何更改我的架构和/或唯一性约束,以便它允许多个 NULL 值,同时仍检查实际数据的唯一性?

14 回答

  • 117

    SQL Server 2008 And Up

    只需过滤一个唯一索引:

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

    In Lower Versions, A Materialized View Is Still Not Required

    对于SQL Server 2005及更早版本,您可以在没有视图的情况下执行此操作 . 我刚刚添加了一个独特的约束,就像你要求我的一张 table 一样 . 鉴于我想要列 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及更高版本中,如果可能,请务必使用已过滤的解决方案!

    Controversy

    请注意,一些数据库专业人员会将此视为"surrogate NULLs"的情况,这肯定存在问题(主要是由于试图确定某些内容是真实值或缺失数据的替代值的问题;数字也可能存在问题非NULL代理值的乘法就像疯了一样) .

    但是,我认为这种情况有所不同 . 我正在添加的计算列永远不会用于确定任何内容 . 它没有任何意义,并且没有编码在其他正确定义的列中没有单独找到的信息 . 永远不应该选择或使用它 .

    所以,我的故事是这不是代理NULL,并且我实际上想要非NULL值用于除了欺骗 UNIQUE 索引忽略NULL之外的任何目的,我们的用例没有任何正常情况下出现的问题代理NULL创建 .

    所有这一切,我没有使用索引视图的问题 - 但它带来了一些问题,如使用 SCHEMABINDING 的要求 . 在基表中添加新列很有趣(您至少必须删除索引,然后删除视图或将视图更改为不受模式限制) . 查看完整(长)list of requirements for creating an indexed view in SQL Server (2005)(也是更高版本),(2000) .

    Update

    如果您的列是数字,则可能存在确保使用 Coalesce 的唯一约束不会导致冲突的挑战 . 在这种情况下,有一些选择 . 一个可能是使用负数,将"surrogate NULLs"仅设置在负范围内,而"real values"仅设置在正范围内 . 或者,可以使用以下模式 . 在表 Issue (其中 IssueIDPRIMARY 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 .

  • 1

    它也可以在设计师中完成

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

    capture

  • -1

    您不能使用 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
    
  • -1

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

    您可以像这样创建视图:

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

    对于正在使用 Microsoft SQL Server Manager 且想要创建Unique但Nullable索引的人,您可以按照通常在新索引的索引属性中创建唯一索引,从左侧面板中选择"Filter",然后输入您的过滤器(即你的where子句) . 它应该是这样的:

    ([YourColumnName] IS NOT NULL)
    

    这适用于MSSQL2012

  • 2

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

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

    每个非null更新和插入失败,错误如下:

    UPDATE失败,因为以下SET选项具有不正确的设置:'ARITHABORT' .

    我在MSDN找到了这个

    在计算列或索引视图上创建或更改索引时,SET ARITHABORT必须为ON . 如果SET ARITHABORT为OFF,则具有计算列或索引视图索引的表上的CREATE,UPDATE,INSERT和DELETE语句将失败 .

    所以为了让这个工作正常,我做到了这一点

    右键单击[数据库] - >属性 - >选项 - >其他选项 - >其他 - >算术中止已启用 - > true

    我相信可以在代码中使用设置此选项

    ALTER DATABASE "DBNAME" SET ARITHABORT ON
    

    但我没有测试过这个

  • -1

    您正在寻找的确实是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来强制执行约束 .

  • 9

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

    SQL Server 2008

    您可以使用 WHERE 子句创建一个接受多个NULL的唯一索引 . 见answer below .

    在SQL Server 2008之前

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

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

  • 4

    也许考虑一个“ INSTEAD OF ”触发器并自己检查?使用列上的非聚集(非唯一)索引来启用查找 .

  • 6

    创建一个仅选择非 NULL 列的视图,并在视图上创建 UNIQUE INDEX

    CREATE VIEW myview
    AS
    SELECT  *
    FROM    mytable
    WHERE   mycolumn IS NOT NULL
    
    CREATE UNIQUE INDEX ux_myview_mycolumn ON myview (mycolumn)
    

    请注意,您需要在视图而不是表上执行 INSERTUPDATE .

    您可以使用 INSTEAD OF 触发器执行此操作:

    CREATE TRIGGER trg_mytable_insert ON mytable
    INSTEAD OF INSERT
    AS
    BEGIN
            INSERT
            INTO    myview
            SELECT  *
            FROM    inserted
    END
    
  • 4
    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];
    
  • 27

    这段代码如果你用textBox创建一个注册表单并使用insert和你的textBox是空的,你点击提交按钮 .

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

    如前所述,SQL Server在 UNIQUE CONSTRAINT 时没有实现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])
    )
    

相关问题