oracle 如何约束多列以防止重复，但忽略空值？

How can I constrain multiple columns to prevent duplicates, but ignore null values?

提问by Chris

Here's a little experiment I ran in an Oracle database (10g). Aside from (Oracle's) implementation convenience, I can't figure out why some insertions are accepted and others rejected.

这是我在 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

Assuming that it makes sense to occasionally have some rows with some column values unknown, I can think of two possible use cases involving preventing duplicates:
1. I want to reject duplicates, but accept when any constrained column's value is unknown.
2. I want to reject duplicates, even in cases when a constrained column's value is unknown.

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

Apparently Oracle implements something different though:
3. Reject duplicates, but accept (only) when allconstrained column values are unknown.

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

I can think of ways to make use of Oracle's implementation to get to use case (2) -- for example, have a special value for "unknown", and make the columns non-nullable. But I can't figure out how to get to use case (1).

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

In other words, how can I get Oracle to act like this?

换句话说，我怎样才能让 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

采纳答案by Chris

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

A functional index! Basically I just needed to make sure all the tuples I want to ignore (ie - accept) get translated to all nulls. Ugly, but not butt ugly. Works as desired.

一个功能指标！基本上我只需要确保我想忽略的所有元组（即 - 接受）被转换为所有空值。丑，但不丑。根据需要工作。

Figured it out with the help of a solution to another question: How to constrain a database table so only one row can have a particular value in a column?

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

So go there and give Tony Andrews points too. :)

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

回答by DCookie

Try a function-based index:

尝试基于函数的索引：

create unique index sandbox_idx on sandbox(CASE WHEN a IS NULL THEN NULL WHEN b IS NULL THEN NULL ELSE a||','||b END);

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

There are other ways to skin this cat, but this is one of them.

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

回答by Steve Kass

I'm not an Oracle guy, but here's an idea that should work, if you can include a computed column in an index in Oracle.

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

Add an additional column to your table (and your UNIQUE index) that is computed as follows: it's NULL if both a and b are non-NULL, and it's the table's primary key otherwise. I call this additional column "nullbuster" for obvious reasons.

向您的表（和您的 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);

I gave this example a number of times around 2002 or so in the Usenet group microsoft.public.sqlserver.programming. You can find the discussions if you search groups.google.com for the word "nullbuster". The fact that you're using Oracle shouldn't matter much.

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

P.S.In SQL Server, this solution is pretty much superseded by filtered indexes:

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

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

The thread you referenced suggests that Oracle doesn't give you this option. Does it also not have the possibility of an indexed view, which is another alternative?

您引用的线程表明 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);

回答by Petros

I guess you can then.

我想你可以。

Just for the record though, I leave my paragraph to explain why Oracle behaves like that if you have a simple unique index on two columns:

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

Oracle will never accept two (1, null) pairs if the columns are uniquely indexed.

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

A pair of 1 and a null, is considered an "indexable" pair. A pair of two nulls cannot be indexed, that's why it lets you insert as many null,null pairs as you like.

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

(1, null) gets indexed because 1 can be indexed. Next time you try to insert (1, null) again, 1 is picked up by the index and the unique constraint is violated.

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

(null,null) isn't indexed because there is no value to be indexed. That's why it doesn't violate the unique constraint.

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