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

小编典典

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

sql

我正在尝试在表中的两个字段上创建唯一约束。但是，很可能一个人将为空。我只要求如果它们都不为null（name永远不会为null），则它们必须是唯一的。

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

忽略表和字段名称的语义以及这是否有意义-我刚刚做了一些补充。

有没有一种方法可以在这些字段上创建唯一约束，从而对两个非null值强制执行唯一性，但是如果有多个条目name（非null和null），email则可以忽略呢？

阅读 333

2021-03-23

共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>

2021-03-23