I do not really understand why Oracle doesn't allow to enforce unique keys in some cases.
Let's say I have a table
create table t (t_type not null, t_key not null, id not null, value not null)
partition by list (t_type)
(
partition type1 values ('ppp1'),
partition rest values (default)
) as
select 'ppp'||rownum, rownum, rownum, rownum from dual connect by rownum <= 100;
And following variations of indexes for unique keys
-- 1
create index idx_1 on t(t_key, id);
alter table t add constraint t_uk unique (t_key) using index idx_1;
alter table t drop constraint t_uk drop index;
-- 2
create index idx_1 on t(t_key, id, t_type);
alter table t add constraint t_uk unique (t_key) using index idx_1;
alter table t drop constraint t_uk drop index;
-- 3
create index idx_1 on t(t_key, id, t_type) local;
alter table t add constraint t_uk unique (t_key) using index idx_1;
drop index idx_1;
-- 4
create unique index idx_1 on t(t_key, id);
alter table t add constraint t_uk unique (t_key) using index idx_1;
drop index idx_1;
-- 5
create unique index idx_1 on t(t_key, id, t_type);
alter table t add constraint t_uk unique (t_key) using index idx_1;
drop index idx_1;
-- 6
create unique index idx_1 on t(t_key, id, t_type) local;
alter table t add constraint t_uk unique (t_key) using index idx_1;
drop index idx_1;
-- 7
create index idx_1 on t(t_key, t_type, id) local;
alter table t add constraint t_uk unique (t_key, t_type) using index idx_1;
alter table t drop constraint t_uk drop index;
-- 8
create unique index idx_1 on t(t_key, t_type, id) local;
alter table t add constraint t_uk unique (t_key, t_type) using index idx_1;
drop index idx_1;
I've got following questions:
1. First and second cases were successfully executed while 4th and 5th failed.
The only difference is that global indexes in 4th and 5th cases are unique.
It's not clear why "ORA-14196: Specified index cannot be used to enforce the constraint." occurred.
2. Index in third case is similar to the one in second with the only difference that it's local.
Again it's not clear why it failed with ORA-14196.
3. And the last question why ORA-14196 has arisen in 8th case while in 7th it has not.
The only difference is that local index in 8th case is unique.
Thanks