Systems Engineering and RDBMS

UnIndexed Foreign Keys – II

Posted by decipherinfosys on December 3, 2008

In some of our previous posts, we have covered the issues that unindexed foreign keys can cause. You can read these posts to get more information on those:

  1. Back to the Basics: Foreign Keys
  2. Un-Indexed Foreign Keys
  3. Blocking on Foreign Keys

A reader asked us whether it is always required to index the FKs or are there some guidelines that can be given in order to not do it under certain scenarios? There are some scenarios under which you do not need to have indexes on the FKs:

a) When you do not delete records from the parent table especially when you do not have the “ON DELETE CASCADE” rule set.

b) When you do not join from the parent table to the child table.

c) When you do not update the unique key (primary key in most cases) in the parent table. This is not done mostly since it would mean a bad selection of the Primary Key column.

d) And if you never query that table directly based on that FK column(s).

e) The child table is a small table and is used only for lookups.

So, if all these conditions above are met in your environment, then there is no need to index those FK columns but else it is usually a good idea to index your foreign keys.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: