Wednesday, February 08, 2006

RDBMS - Foreign key indexing

To decide if a foreign key needs to be indexed or not, I follow a simple rule:

I always/only create an index on a foreign key whether:

1 - A deletion on the parent table is allowed and it triggers a cascade delete on the child table
2 - There's need to perform JOIN queries from the parent to the child

In the first situation, an unindexed foreign key will force a full table scan for each parent record deleted. In the second situation, a lack of the foreign key index in the child table will slow down join queries.

I rarely find suitable to create indexes on foreign keys in other situations though.