如何限制多列以防止重复，但忽略空值？

Question

这是我在 Oracle 数据库 (10g) 中运行的一个小实验。 除了（Oracle）实现的便利性之外，我不明白为什么有些插入被接受而另一些插入被拒绝。

create table sandbox(a number(10,0), b number(10,0));
create unique index sandbox_idx on sandbox(a,b);

insert into sandbox values (1,1); -- accepted
insert into sandbox values (1,2); -- accepted
insert into sandbox values (1,1); -- rejected

insert into sandbox values (1,null); -- accepted
insert into sandbox values (2,null); -- accepted
insert into sandbox values (1,null); -- rejected

insert into sandbox values (null,1); -- accepted
insert into sandbox values (null,2); -- accepted
insert into sandbox values (null,1); -- rejected

insert into sandbox values (null,null); -- accepted
insert into sandbox values (null,null); -- accepted

假设偶尔有一些行的某些列值未知是有意义的，我可以想到两个可能的用例，涉及防止重复：
1. 我想拒绝重复项，但在任何受约束列的值未知时接受。
2. 我想拒绝重复项，即使在约束列的值未知的情况下也是如此。

显然 Oracle 实现了一些不同的东西：
3. 拒绝重复项，但（仅）当所有约束列值未知时接受。

我可以想出一些方法来利用 Oracle 的实现来实现用例 (2)——例如，为“未知”设置一个特殊值，并使列不可为空。 但我不知道如何进入用例（1）。

换句话说，我怎样才能让Oracle这样做呢？

create table sandbox(a number(10,0), b number(10,0));
create unique index sandbox_idx on sandbox(a,b);

insert into sandbox values (1,1); -- accepted
insert into sandbox values (1,2); -- accepted
insert into sandbox values (1,1); -- rejected

insert into sandbox values (1,null); -- accepted
insert into sandbox values (2,null); -- accepted
insert into sandbox values (1,null); -- accepted

insert into sandbox values (null,1); -- accepted
insert into sandbox values (null,2); -- accepted
insert into sandbox values (null,1); -- accepted

insert into sandbox values (null,null); -- accepted
insert into sandbox values (null,null); -- accepted

Answer 1

尝试基于函数的索引：

在沙箱上创建唯一索引 sandbox_idx(CASE WHEN a IS NULL THEN NULL WHEN b IS NULL THEN NULL ELSE a||','||b END);

还有其他方法可以给这只猫剥皮，但这就是其中之一。

Answer 2

create unique index sandbox_idx on sandbox
 (case when a is null or b is null then null else a end,
  case when a is null or b is null then null else b end);

功能指标！ 基本上我只需要确保我想忽略（即接受）的所有元组都被转换为所有空值。 丑，但屁股不丑。 按预期工作。

在另一个问题的解决方案的帮助下找到了答案：如何约束数据库表，使某一列中只有一行可以有特定值？

所以去那里也给托尼安德鲁斯点。 :)

Answer 3

我不是 Oracle 人员，但如果您可以在 Oracle 的索引中包含计算列，那么这里有一个应该可行的想法。

向表（以及 UNIQUE 索引）中添加一个附加列，计算如下：如果 a 和 b 都非 NULL，则该列为 NULL，否则为表的主键。 出于显而易见的原因，我将这个附加列称为“nullbuster”。

alter table sandbox add nullbuster as 
  case when a is null or b is null then pk else null end;
create unique index sandbox_idx on sandbox(a,b,pk);

大约 2002 年左右，我在 Usenet 组 microsoft.public.sqlserver.programming 中多次给出了这个示例。 如果您在 groups.google.com 中搜索“nullbuster”一词，就可以找到相关讨论。 您使用 Oracle 的事实并不重要。

PS 在 SQL Server 中，此解决方案几乎已被过滤索引所取代：

create unique index sandbox_idx on sandbox(a,b)
(where a is not null and b is not null);

您引用的线程表明 Oracle 没有为您提供此选项。 它是否也没有索引视图（这是另一种选择）的可能性？

create view sandbox_for_unique as
select a, b from sandbox
where a is not null and b is not null;

create index sandbox_for_unique_idx on sandbox_for_unique(a,b);

Answer 4

我想你可以。

不过，为了记录，我留下我的段落来解释为什么如果在两列上有一个简单的唯一索引，Oracle 会表现得像这样：

如果列是唯一索引的，Oracle 将永远不会接受两个 (1, null) 对。

一对 1 和 null 被视为“可索引”对。 一对两个 null 无法建立索引，这就是为什么它允许您插入任意数量的 null,null 对。

(1, null) 被索引，因为 1 可以被索引。 下次您尝试再次插入 (1, null) 时，索引会选取 1，并且会违反唯一约束。

(null,null) 未编入索引，因为没有要编入索引的值。 这就是为什么它不违反唯一约束。