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

Question

我正在尝试对表中的两个字段创建唯一约束。然而，很可能该值为空。我只要求它们在两者都不为空时是唯一的(name 永远不会为空)。

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

忽略表和字段名称的语义以及这是否有意义 - 我只是编造了一些。

有没有办法在这些字段上创建唯一约束，以强制两个非空值的唯一性，但如果存在多个 name 不为空且 email< 的条目，则忽略 为空？

这个问题是针对 SQL Server 的，我希望答案不一样: How do I create a unique constraint that also allows nulls?

Answer 1

我们可以使用基于函数的索引来做到这一点。下面使用了 NVL2()，如您所知，如果表达式不为 null，则返回一个值；如果表达式为 null，则返回一个不同的值。您可以使用 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, 'apc@example.com')
  2  /

1 row created.

SQL> insert into blah values (null, 'apc@example.com')
  2  /

1 row created.

SQL> insert into blah values ('APC', 'apc@example.com')
  2  /

1 row created.

SQL> insert into blah values ('APC', 'apc@example.com')
  2  /
insert into blah values ('APC', 'apc@example.com')
*
ERROR at line 1:
ORA-00001: unique constraint (APC.BLAH_UIDX) violated


SQL>

编辑

因为在您的场景中名称将始终被填充，您只需要这样的索引:

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

Index created.

SQL>