如何在Oracle中创建一个唯一索引但忽略空值？

Question

我正在尝试在表的两个字段上创建唯一约束条件。但是，其中一个字段很可能为null。我只需要在两个字段都不为null时它们才是唯一的（name字段永远不会为null）。

create unique index "name_and_email" on user(name, email);

忽略表名和字段名的语义以及它们是否有意义 - 我只是随便编了一些。

是否有办法在这些字段上创建一个唯一约束，以确保两个非空值的唯一性，但忽略多个条目中 name 不为空且 email 为空的情况？

这个问题是针对 SQL Server 的，我希望答案不同于： 如何创建允许 null 值的唯一约束？

- Brian Ramsay

Answer 1

我们可以使用函数索引来实现这一点。以下代码使用了NVL2()函数，它会在表达式不为空时返回一个值，在表达式为空时返回另一个值。您也可以使用CASE()函数替代。

SQL> create table blah (name varchar2(10), email varchar2(20))
  2  /

Table created.

SQL> create unique index blah_uidx on blah
  2      (nvl2(email, name, null), nvl2(name, email, null))
  3  /

Index created.

SQL> insert into blah values ('APC', null)
  2  /

1 row created.

SQL> insert into blah values ('APC', null)
  2  /

1 row created.

SQL> insert into blah values (null, '[e-mail]')
  2  /

1 row created.

SQL> insert into blah values (null, '[e-mail]')
  2  /

1 row created.

SQL> insert into blah values ('APC', '[e-mail]')
  2  /

1 row created.

SQL> insert into blah values ('APC', '[e-mail]')
  2  /
insert into blah values ('APC', '[e-mail]')
*
ERROR at line 1:
ORA-00001: unique constraint (APC.BLAH_UIDX) violated


SQL>

编辑

因为在您的情况下，name 字段将始终被填充，所以您只需要像这样使用索引：

SQL> create unique index blah_uidx on blah
  2      (nvl2(email, name, null), email)
  3  /

Index created.

SQL> 

- APC

Answer 2

我不知道有多少人仍然会看到这个答案，但至少在最新版本的Oracle中允许在唯一索引中有多行为null，并且不需要接受的答案。

- broll

---

请问您能提供关于这个的文档参考吗？我找不到相关资料，根据我的经验，可能是版本不对。 - Sepster