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, '[email protected]') 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 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.

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', '[email protected]') 2 / 1 row created.

SQL> insert into blah values ('APC', 'apc@example. Com') 2 / insert into blah values ('APC', '[email protected]') * ERROR at line 1: ORA-00001: unique constraint (APC. BLAH_UIDX) violated SQL> 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.

1, FBI to the rescue ;-) – DCookie Sep 3 '09 at 17:25 +1 Nice trick! – dpbradley Sep 3 '09 at 17:31.

I cant really gove you an answer,but what I can give you is a way to a solution, that is you have to find the anglde that you relate to or peaks your interest. A good paper is one that people get drawn into because it reaches them ln some way.As for me WW11 to me, I think of the holocaust and the effect it had on the survivors, their families and those who stood by and did nothing until it was too late.