Partial uniqueness

“ 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);

”

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
  );