Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Enterprise
Expertise: Beginner
May 4, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

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.

Jayanta Sengupta
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date