Indexing Foreign Key Columns in Child Tables

Indexing Foreign Key Columns in Child Tables

Although an index is not mandatory on your foreign keys, if they are not indexed you will cause additional locking when certain Data Manipulation Language statements are executed against the parent table.

Without the index, checking for existing child records when attempting a delete of a master record (cascade or restrict, makes no difference) incurs a full table scan on the child table. Thus, the transaction on the parent table acquires a “share” lock on the child table, thereby, blocking all other transactions from performing DML on the child table for the duration of the transaction.

Unless your parent table is static, you should maintain an index on the child table’s foreign key column(s) if you wish to avoid performance problems that can occur when the parent table is being modified concurrently with other transactions that modify the parent or child table.

See also  Is Machine Learning Automating Creativity in Graphic Design?

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist