title: How can I constrain multiple columns to prevent duplicates, but ignore null values?
body: <p>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.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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:<br>
   1. I want to reject duplicates, but accept when any constrained column's value is unknown.<br>
   2. I want to reject duplicates, even in cases when a constrained column's value is unknown.  </p>
  
  <p>Apparently Oracle implements something different though:<br>
   3. Reject duplicates, but accept (only) when <em>all</em> constrained column values are unknown.  </p>
  
  <p>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).</p>
  
  <p>In other words, how can I get Oracle to act like this?</p>
  
  <pre><code>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
  </code></pre>