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.

Advertisements

2 thoughts on “Partial uniqueness

  1. 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?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s