Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

partitioned table and unique keys

Alex.UAJul 1 2014 — edited Jul 6 2014

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

This post has been answered by Richard Foote-Oracle on Jul 2 2014
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 3 2014
Added on Jul 1 2014
9 comments
1,331 views