Can a foreign key index be part of another index or should it be standalone?

user_1254644 0
Viewed 36

I have an index organized table in Oracle 18 with the primary key

PRIMARY KEY (ISSUE_ID, LOCATION_ID_FIN, PRODUCTNUMBER, LOCATION_ID_COMPONENT, LOCAL_PART_NUMBER)

There is an foreign key to the issues table (issue_id) and two towards the locations table (LOCATION_ID_FIN and LOCATION_ID_COMPONENT).

I would not create a separate index on issue_id, because it is on first place, but should I create separate foreign key indexes for LOCATION_ID_FIN and LOCATION_ID_COMPONENT or is it enough that those columns are contained in the primary key?

(this question assumes that a foreign key index is needed to avoid any locking (tm-contention))

Database Version is 18, migrating to 19c the next weeks

indexing foreign-keys oracle
1 Answers

Yes, you do need the index that starts with the foreign key columns. A way to prove this (from Tom Kyte's book Expert One-On-One Oracle) is to do this:

alter table issues disable table lock;

Now if you try to update the parent table like this:

update locations set location_id_fin = <new value>;

you will get this error:

ORA-00069: cannot acquire lock -- table locks disabled for CHILD

However once you add the index that begins with the foreign key columns then you will get the expected error:

ORA-02292: integrity constraint (xxx) violated - child record found

user_187470
Related