Reputation: 1233
I have a table with three columns - c_id, o_id, f_id. The combination of the three ids has to be unique as long as f_id is not 3.
After reading similar questions I tried the following :
create unique index my_index on my_table (
decode (f_id, !3, c_id, null),
decode (f_id, !3, o_id, null),
decode (f_id, !3, f_id, null));
but I'm getting a missing expression error. I also tried with
create unique index my_index on my_table ((
case
when f_id <> 3 then c_id
when f_id <> 3 then o_id
when f_id <> 3 then f_id
else null
end));
but for this one I'm getting cannot create index; duplicate keys found
Originally I tried
when f_id <> 3 then (c_id, o_id, f_id)
but that didn't work at all.
Anyhow, something is probably wrong with the index as the table doesn't seem to have any duplicates, I get no records with
select c_id, o_id, f_id, count(*) as dupes
from my_table
where f_id <> 3
having count(*) > 1
group by c_id, o_id, f_id;
I'm kinda running blind with these FBIs, so any help would be appreciated.
Upvotes: 1
Views: 2227
Reputation: 21170
Alternative approach with only one column index:
create unique index all_but_3 on tst (case when nvl(f_id,0) != 3 then o_id||'.'||c_id||'.'||f_id end);
Test
insert into tst(c_id, o_id, f_id) values(3,3,3);
insert into tst(c_id, o_id, f_id) values(3,3,3); -- OK
insert into tst(c_id, o_id, f_id) values(3,3,2);
insert into tst(c_id, o_id, f_id) values(3,3,2); -- ORA-00001: unique constraint xxx violated
insert into tst(c_id, o_id, f_id) values(3,3,null);
insert into tst(c_id, o_id, f_id) values(3,3,null); -- ORA-00001: unique constraint xxx violated
insert into tst(c_id, o_id, f_id) values(null,null,2);
insert into tst(c_id, o_id, f_id) values(null,null,2); -- -- ORA-00001: unique constraint xxx violated
Upvotes: 1
Reputation: 1271241
You seem to want a 3-part index:
create unique index my_index
on my_table (case when f_id <> 3 then c_id end,
case when f_id <> 3 then o_id end,
case when f_id <> 3 then f_id end
);
Upvotes: 2