SQL 我可以有一个“条件唯一”吗？表上的约束？

Question

在我的职业生涯中，这个问题已经出现过好几次了，但我当地的同行似乎都无法回答这个问题。假设我有一个表，其中有一个“描述”字段，该字段是候选键，但有时用户会在过程中途停止。因此，对于 25% 的记录，该值可能为 null，但对于所有非 NULL 的记录，它必须是唯一的。

另一个例子可能是一个表，它必须维护一条记录的多个“版本”，并且一个位值指示哪一个是“活动”版本。因此，“候选密钥”始终会被填充，但可能存在三个相同的版本（活动位为 0），并且只有一个版本是活动的（活动位为 1）。

我有替代方法来解决这些问题（在第一种情况下，在存储过程或业务层中强制执行规则代码，在第二种情况下，使用触发器填充存档表，并在需要历史记录时对表进行 UNION） 。我不需要替代方案（除非有明显更好的解决方案），我只是想知道是否有任何 SQL 风格可以通过这种方式表达“条件唯一性”。我正在使用 MS SQL，所以如果有办法做到这一点，那就太好了。我主要只是在学术上对这个问题感兴趣。

Answer 1

如果您使用的是 SQL Server 2008，索引过滤器可能是您的解决方案：

http:// msdn.microsoft.com/en-us/library/ms188783.aspx

这就是我使用多个 NULL 值强制执行唯一索引的方法

CREATE UNIQUE INDEX [IDX_Blah] ON [tblBlah] ([MyCol]) WHERE [MyCol] IS NOT NULL

Answer 2

对于尚未完成的描述，我不会将其与最终的描述放在同一个表中。最终表将在描述上具有唯一索引或主键。

在活动/非活动的情况下，我可能会像使用“存档”或“历史”表一样拥有单独的表，但至少在 MS SQL Server 中执行此操作的另一种可能方法是通过使用索引视图：

CREATE TABLE Test_Conditionally_Unique
(
    my_id   INT NOT NULL,
    active  BIT NOT NULL DEFAULT 0
)
GO
CREATE VIEW dbo.Test_Conditionally_Unique_View
WITH SCHEMABINDING
AS
    SELECT
        my_id
    FROM
        dbo.Test_Conditionally_Unique
    WHERE
        active = 1
GO
CREATE UNIQUE CLUSTERED INDEX IDX1 ON Test_Conditionally_Unique_View (my_id)
GO

INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (1, 0)
INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (1, 0)
INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (1, 0)
INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (1, 1)
INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (2, 0)
INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (2, 1)
INSERT INTO dbo.Test_Conditionally_Unique (my_id, active)
VALUES (2, 1)    -- This insert will fail

您也可以对 NULL/Valued 描述使用相同的方法。

Answer 3

感谢您的评论，这个答案的最初版本是错误的。

下面是一个使用计算列的技巧，它可以在 SQL Server 中有效地允许可空的唯一约束：

create table NullAndUnique 
    (
    id int identity, 
    name varchar(50),
    uniqueName as case 
        when name is null then cast(id as varchar(51)) 
        else name + '_' end,
    unique(uniqueName)
    )

insert into NullAndUnique default values
insert into NullAndUnique default values -- Works
insert into NullAndUnique default values -- not accidentally :)
insert into NullAndUnique (name) values ('Joel')
insert into NullAndUnique (name) values ('Joel') -- Boom!

当 name 为空时，它基本上使用 id。 + '_' 是为了避免名称可能是数字的情况，例如 1，它可能与 id 冲突。

Answer 4

我不完全了解您的预期用途或表格，但您可以尝试使用一对一关系。将这个“有时”唯一的列拆分到一个新表中，在新表中的该列上创建 UNIQUE 索引，并使用原始表 PK FK 返回原始表。仅当“唯一”数据应该存在时，此新表中才会有一行。

旧表：

TableA
ID    pk
Col1  sometimes unique
Col...

新表：

TableA
ID
Col...

TableB
ID   PK, FK to TableA.ID
Col1 unique index

Answer 5

甲骨文确实如此。 Oracle 中索引中的 B 树不会对全空键进行索引，Oracle 使用 B 树索引来强制执行唯一约束。

假设希望基于 ACTIVE_FLAG 设置为 1 来版本 ID_COLUMN：

CREATE UNIQUE INDEX idx_versioning_id ON mytable 
  (CASE active_flag WHEN 0 THEN NULL ELSE active_flag END,
   CASE active_flag WHEN 0 THEN NULL ELSE id_column   END);