Reusing index in constraints

oracle

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.

Best Answer

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.

Related Question