How to create foreign key index and how it works

Suppose a large number of the queries in your application use foreign key columns as predicates in the WHERE clause. Therefore, for performance reasons, you want to ensure that you have all foreign key columns indexed.

So unlike primary key constraints, Oracle does not automatically create indexes on foreign key columns. For example, say you have a requirement that every record in the ADDRESS table be assigned a corresponding CUST_ID column that exists in the CUST table. To enforce this relationship, you create a foreign key constraint on the ADDRESS table as follows:

alter table address add constraint addr_fk1
foreign key (cust_id) references cust(cust_id);

You realize the foreign key column is used extensively when joining the CUST and ADDRESS tables and that an index on the foreign key column will dramatically increase performance. You have to manually create an index in this situation. For example, a regular B-tree index is created on the foreign key column of CUST_ID in the ADDRESS table:

SQL> create index addr_fk1 on address(cust_id);

You don’t have to name the index the same as the foreign key name (as we did in the prior lines of code). It’s a personal preference as to whether you do that. We feel it’s easier to maintain environments when the constraint and corresponding index have the same name.

How It Works

Foreign keys exist to ensure that when inserting into a child table, a corresponding parent table record exists. This is the mechanism to guarantee that data conforms to parent/child business relationship rules. From a performance perspective, it’s usually a good idea to create an index on foreign key columns. This is because parent/child tables are frequently joined on the foreign key column(s) in the child table to the primary key column(s) in the parent table—for example:

select
a.last_name, a.first_name, b.state
from cust a
,address b
where a.cust_id = b.cust_id;

In most scenarios, the Oracle query optimizer will choose to use the index on the foreign key column to identify the child records that are required to satisfy the results of the query. If no index exists, Oracle has to perform a full table scan on the child table.

If you’ve inherited a database, then it’s prudent to check if columns with foreign key constraints defined on them have a corresponding index. The following query displays indexes associated with foreign key constraints: