How can I constrain multiple columns to prevent duplicates, but ignore null values?

Question

Here's a little experiment I ran in an Oracle database (10g). Aside from (Oracle's) implementation convenience, I can't figure out why some insertions are accepted and others rejected.

create table sandbox(a number(10,0), b number(10,0));
create unique index sandbox_idx on sandbox(a,b);

insert into sandbox values (1,1); -- accepted
insert into sandbox values (1,2); -- accepted
insert into sandbox values (1,1); -- rejected

insert into sandbox values (1,null); -- accepted
insert into sandbox values (2,null); -- accepted
insert into sandbox values (1,null); -- rejected

insert into sandbox values (null,1); -- accepted
insert into sandbox values (null,2); -- accepted
insert into sandbox values (null,1); -- rejected

insert into sandbox values (null,null); -- accepted
insert into sandbox values (null,null); -- accepted

Assuming that it makes sense to occasionally have some rows with some column values unknown, I can think of two possible use cases involving preventing duplicates:
1. I want to reject duplicates, but accept when any constrained column's value is unknown.
2. I want to reject duplicates, even in cases when a constrained column's value is unknown.

Apparently Oracle implements something different though:
3. Reject duplicates, but accept (only) when all constrained column values are unknown.

I can think of ways to make use of Oracle's implementation to get to use case (2) -- for example, have a special value for "unknown", and make the columns non-nullable. But I can't figure out how to get to use case (1).

In other words, how can I get Oracle to act like this?

create table sandbox(a number(10,0), b number(10,0));
create unique index sandbox_idx on sandbox(a,b);

insert into sandbox values (1,1); -- accepted
insert into sandbox values (1,2); -- accepted
insert into sandbox values (1,1); -- rejected

insert into sandbox values (1,null); -- accepted
insert into sandbox values (2,null); -- accepted
insert into sandbox values (1,null); -- accepted

insert into sandbox values (null,1); -- accepted
insert into sandbox values (null,2); -- accepted
insert into sandbox values (null,1); -- accepted

insert into sandbox values (null,null); -- accepted
insert into sandbox values (null,null); -- accepted

Oas

Perfect example of a good question (plus it's one I needed answered!) – Rodmur

Answer 1

create unique index sandbox_idx on sandbox
 (case when a is null or b is null then null else a end,
  case when a is null or b is null then null else b end);

A functional index! Basically I just needed to make sure all the tuples I want to ignore (ie - accept) get translated to all nulls. Ugly, but not butt ugly. Works as desired.

Figured it out with the help of a solution to another question: How to constrain a database table so only one row can have a particular value in a column?

So go there and give Tony Andrews points too. :)

Oas

I don't find this ugly at all. Much cleaner IMHO than the accepted answer, which can mush 2 columns together, maybe not even the same datatype, to create some Frankenstein unique key (not that I wouldn't have used it if you hadn't shown me the correct syntax for multicolumn). – Rodmur

Answer 2

Try a function-based index:

create unique index sandbox_idx on sandbox(CASE WHEN a IS NULL THEN NULL WHEN b IS NULL THEN NULL ELSE a||','||b END);

There are other ways to skin this cat, but this is one of them.

Turd

Answer 3

I'm not an Oracle guy, but here's an idea that should work, if you can include a computed column in an index in Oracle.

Add an additional column to your table (and your UNIQUE index) that is computed as follows: it's NULL if both a and b are non-NULL, and it's the table's primary key otherwise. I call this additional column "nullbuster" for obvious reasons.

alter table sandbox add nullbuster as 
  case when a is null or b is null then pk else null end;
create unique index sandbox_idx on sandbox(a,b,pk);

I gave this example a number of times around 2002 or so in the Usenet group microsoft.public.sqlserver.programming. You can find the discussions if you search groups.google.com for the word "nullbuster". The fact that you're using Oracle shouldn't matter much.

P.S. In SQL Server, this solution is pretty much superseded by filtered indexes:

create unique index sandbox_idx on sandbox(a,b)
(where a is not null and b is not null);

The thread you referenced suggests that Oracle doesn't give you this option. Does it also not have the possibility of an indexed view, which is another alternative?

create view sandbox_for_unique as
select a, b from sandbox
where a is not null and b is not null;

create index sandbox_for_unique_idx on sandbox_for_unique(a,b);

Telephonic

Good answer though a bit too baroque for my application. To answer your questions, Oracle doesn't have filtered indexes but your "indexed view" seems to be covered by materialized views in Oracle, which can be indexed and often are for referential integrity. – Rodmur

Answer 4

I guess you can then.

Just for the record though, I leave my paragraph to explain why Oracle behaves like that if you have a simple unique index on two columns:

Oracle will never accept two (1, null) pairs if the columns are uniquely indexed.

A pair of 1 and a null, is considered an "indexable" pair. A pair of two nulls cannot be indexed, that's why it lets you insert as many null,null pairs as you like.

(1, null) gets indexed because 1 can be indexed. Next time you try to insert (1, null) again, 1 is picked up by the index and the unique constraint is violated.

(null,null) isn't indexed because there is no value to be indexed. That's why it doesn't violate the unique constraint.

Duvall

This is just one reason for the implementation of function-based indexes in Oracle. It allows the business to tailor the index to their own business rules. – Turd