Systems Engineering and RDBMS

Archive for December 3rd, 2008

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.

Posted in DB2 LUW, Oracle, SQL Server | Leave a Comment »