Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm trying to understand how Oracle determine if the index can be used for constraint. For example, the following works :

create table temp11
(id int not null,
val varchar2(10),
CONSTRAINT PK_temp11 primary key (id) using index 
(create index IDX_temp11 ON temp11(id,val))
);
--Also, I can re-use the same index for another unique constraint :
ALTER TABLE temp11 ADD CONSTRAINT UQ_temp11 UNIQUE(id,val) using index IDX_temp11;

But if I change index definition and make it unique, I get error

ORA-14196: Specified index cannot be used to enforce the constraint.

create table temp11
(id int not null,
val varchar2(10),
CONSTRAINT PK_temp11 primary key (id) using index 
(create UNIQUE index IDXU_temp11 ON temp11(id,val))
); -- ORA-14196

** Making val not null doesn't make a difference.
Could anyone explain this behaviour?
I don't see why such an unique index cannot used for enforcing constraint whereas non-unique is accepted. I use Oracle 10 if it matters.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

I understand your frustration with this behavior. There seem to be internal obstacles to implementing this and could lead to larger indexes than are necessary.

  • With a unique index supporting a primary key, constraint validation is done before the unique index is modified and one look-up can verify whether the entry can be inserted or not.1

  • With a non-unique index supporting a primary key, constraint validation is done after changes are made to the non-unique index and multiple look-ups are required to verify whether the entry can be inserted or not.1

A primary key using the leading column of a unique index would require non-unique index constraint logic on a unique index. This would require changes and/or additions to the logic.

If id is unique/primary then (id,val) is inherently unique, and normally you would not need/want a unique constraint/index on (id,val).

You might want a unique index on (id,val) if there were a query referencing (id,val) and you wanted to prevent a table look-up. If this is your situation, you may have to decided which is the lesser tradeoff between allowing the table look-up, increasing the primary key to (id,val) or having two unique indexes. For most situations I suspect the table look-up would be preferable.

share|improve this answer
    
Thank you, it's getting more clear now –  a1ex07 Feb 13 '12 at 21:33
add comment

I dont know if this helps you, But I tried with Different things.. and this works.

create table temp11
(id int not null,
val varchar2(10) not null,
CONSTRAINT PK_temp11 primary key (id,val) using index 
(create unique index IDXU_temp11 ON temp11(id,val))
); 

What I derive from here is your Primary Key index was with (id) i.e 1 col, whereas ure are using the same for id,val 2 cols, Again This is just a wild guess.

share|improve this answer
1  
Thanks for your time, but it doesn't really help. No doubt it works if all pk columns are listed in unique index, but I try to reuse index in more than 1 constraints. Somehow it works with non-unique index(1st example), but not with unique... –  a1ex07 Feb 13 '12 at 13:19
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.