Как создать уникальный индекс в Oracle, но игнорировать нули?

Я пытаюсь создать уникальное ограничение на два поля в таблице. Однако существует высокая вероятность того, что он будет нулевым. Я только требую, чтобы они были уникальными, если оба они не являются нулевыми (name никогда не будет null).

Игнорировать семантику имен таблиц и полей и имеет ли смысл - я только что сделал.

Есть ли способ создать уникальное ограничение для этих полей, которое будет обеспечивать уникальность для двух не нулевых значений, но игнорировать, если есть несколько записей, где name не является нулевым, а email - null?

Ответы

Ответ 1

Мы можем сделать это с помощью функционального индекса. Следующее использует NVL2(), который, как вы знаете, возвращает одно значение, если выражение не является нулевым, а другое значение, если оно равно 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, '[email protected]')
  2  /

1 row created.

SQL> insert into blah values (null, '[email protected]')
  2  /

1 row created.

SQL> insert into blah values ('APC', '[email protected]')
  2  /

1 row created.

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


SQL>

Edit

Поскольку в вашем названии сценария всегда будет заполнено, вам понадобится только индекс:

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

Index created.

SQL>