How can I create a unique index in Oracle but ignore nulls?
We can do this with a function-based index. The following makes use of NVL2()
which, as you know, returns one value if the expression is not null and a different value if it is null. You could use CASE()
instead.
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) violatedSQL>
Edit
Because in your scenario name will always be populated you will only need an index like this:
SQL> create unique index blah_uidx on blah 2 (nvl2(email, name, null), email) 3 /Index created.SQL>