如何限制多列以防止重复，但忽略空值？（针对IT技术问题）

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

- Chris

---

一个好问题的完美例子（而且这正是我需要回答的问题！） - orbfish

Answer 1

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

一个功能性索引！基本上我只需要确保我想要忽略（即-接受）的所有元组都被转换为所有空值。丑陋，但不是非常丑陋。按预期工作。

通过另一个问题的解决方案找到了答案：如何限制数据库表格，使得只有一行可以在某个列中具有特定的值？ 所以去那里并给Tony Andrews点赞吧。 :)

- Chris

---

我并不认为这很丑陋。在我看来，比被接受的答案要干净得多，后者可能会将两列混合在一起，甚至可能不是相同的数据类型，从而创建一些弗兰肯斯坦唯一键（如果你没有向我展示多列的正确语法，我也许会使用它）。 - orbfish

Answer 2

试试使用基于函数的索引:

创建唯一索引sandbox_idx on sandbox(CASE WHEN a IS NULL THEN NULL WHEN b IS NULL THEN NULL ELSE a||','||b END);

还有其他方法解决这个问题，但这是其中之一。

- DCookie

Answer 3

我不是Oracle专家，但以下思路应该可行，如果你可以在Oracle中的索引中包含一个计算列。

向你的表（和唯一索引）添加一个额外的列，其计算方法如下：如果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年左右的美国在线讨论组microsoft.public.sqlserver.programming中多次举过这个例子。如果您搜索单词“nullbuster”，可以找到相关讨论。您使用Oracle并不重要。

P.S. 在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);

- Steve Kass

---

虽然有些复杂，但你的回答很好。回答你的问题，Oracle没有过滤索引，但是你提到的“索引视图”在Oracle中似乎被材料化视图所覆盖，这些视图可以被索引，并且通常用于引用完整性。 - orbfish

Answer 4

我猜你可以这样做。

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

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

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

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

(null,null)没有被索引，因为没有值可以被索引。这就是为什么它不违反唯一约束。

- Petros

---

这只是在Oracle中实现基于函数的索引的一个原因。它允许企业根据自己的业务规则定制索引。 - DCookie