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

我们可以使用基于函数的索引来实现这一点。下面利用 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, '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> 

我不知道还有多少人会被引导到这个答案,但至少在最新版本的Oracle中,在一个唯一索引上允许有多个空行,而接受的答案是不必要的。