Passionate about data

Data and its implications on software design and development.

Create an Index for All FK Columns in the Database

Most of the time I have seen database foreign key constraints on tables without indexes on those columns. Lets say the application is trying to delete a row from the CUSTOMER table

1
DELETE FROM CUSTOMER WHERE CUSTOMERID = 1000;

When the database goes about deleting the customerId of 1000, if there are foreign key constraints defined on customerId, then the database is going to try to find if the customerId of 1000 is used in any of those tables. Lets say ORDER table has the customerId column, the database is going to issue

1
SELECT ... FROM ORDER WHERE CUSTOMERID = 1000;

now if there is no index on ORDER.CUSTOMERID, the database will have to do a full Table scan which is very expensive in terms of IO and resources, imagine customerId being used in lots of tables, the problem just multiplies significantly. In an multiuser scenario, this will lead to deadlocks, since the same tables are being read and locks being applied to find dependend children. Introducing an index on all the columns that are foreign key referenced helps a lot in this case.