Learning is not a spectator sport
Connor McDonald on SQL and the Oracle Database
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.
Ah… but then they say they want the uniqueness to be deferrable …
https://orastory.wordpress.com/2014/06/25/conditional-uniqueness/
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?