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