Hermann_Brunner Hermann_Brunner - 3 months ago 13
SQL Question

out of line sql index creation issue Oracle

Can someone explain me what wrong with the syntax. I want to add constraints primary key and unique but also want to use the same index (composite index).

create table val (
val1 number, val2 number,
constraint val_pk primary key(val1) using index(create index val_index on val(val1,val2)),
constraint val_unique unique (val2) using index val_index);


its giving me an error

create table val (
val1 number, val2 number,
constraint val_pk primary key(val1) using index(create index val_index on val(val1,val2)),
constraint val_unique unique (val2) using index val_index)
Error report -
SQL Error: ORA-14196: Specified index cannot be used to enforce the constraint.
14196. 00000 - "Specified index cannot be used to enforce the constraint."
*Cause: The index specified to enforce the constraint is unsuitable
for the purpose.
*Action: Specify a suitable index or allow one to be built automatically.


Thanks for the answers.

Answer

To enforce primary key or unique constraints, Oracle uses an index.

By default, if a suitable index doesn't already exist, Oracle will automatically create a unique index with the same columns and in the same order as the constraint definition.

However, Oracle is also capable of using non-unique indexes, and the columns don't need to be identical to the ones in the constraint.

However, for an index to be usable, the leading columns of the index need to include all the columns defined in the constraint.

So in your case, if you have an index defined as:

create index val_index on val(val1,val2)

... it can be used to enforce the primary key on val1, because val1 is the leading column in the index.

But it cannot be used to enforce the unique constraint on val2, because val2 is the 2nd column in the index.

On the other hand, it you defined a unique constraint on (val1, val2) or (val2, val1), both of those combinations can use the index successfully, because they are both covered by the leading columns of the index (the 1st and 2nd columns of the index). Of course, having both constraints is redundant, so it doesn't make sense to do that.

In your case, having what are essentially 2 different unique constraints on 2 different columns will require at least 2 indexes. You will not be able to enforce both constraints with a single index.