Partial uniqueness

I had an interesting request recently from a developer.

“ I have a table created as per below

create table C_TEST (
col_1 varchar2(3),
col_2 varchar2(3),
col_3 number
);

The rows defined by col_1, col_2, col_3 must be unique but only when col_3 is present. If col_3 is not present, then we allow anything. Hence if the table is populated like this:

begin
insert into c_test values (‘a’,’b’,null);
insert into c_test values (‘a’,’b’,1);
insert into c_test values (‘a’,’b’,2);
insert into c_test values (‘a’,’c’,1);
insert into c_test values (‘b’,null,1);
insert into c_test values (‘c’,null,null);
insert into c_test values (null,null,null);
insert into c_test values (null,null,1);
insert into c_test values (null,null,2);
end;
/

— then all of the following should fail

insert into c_test values (‘a’,’b’,1);
insert into c_test values (‘b’,null,1);
insert into c_test values (null,null,1);

”

My first thought there is something perhaps out of whack with the design, but who am I to say.

Anyway, we can take advantage of the fact that if entirity of an index key is null, then no value is stored in the index (for a B-tree index). Hence the following definition should satisfy the need:

create unique index c_test_ix on c_test
 (case when col_3 is not null then col_1 end,
  case when col_3 is not null then col_2 end,
  case when col_3 is not null then col_3 end
  );

The CASE statements effectively only bring columns col_1, col_2 and col_3 into play when col_3 is provided.

2 comments

Dom Brooks says:

July 2, 2015 at 6:01 pm

Ah… but then they say they want the uniqueness to be deferrable …
https://orastory.wordpress.com/2014/06/25/conditional-uniqueness/

Arno says:

July 4, 2015 at 2:16 am

I immediately concur on ‘My first thought there is something perhaps out of whack with the design, but who am I to say.’ First thing that occurs when reading this post 🙂
I would suggest to use two base tables (one pk’ed on all three cols’s, the other on only the first two) and a (union all) view (why not for a change), and some (instead of) trigger logic to support this ‘whacky’ design.
Performance will be poor probably, but then again, what to expect with such a design?