When NOT to index a Foreign Key

It's common for people to want to index all of their foreign key columns.  In general this is probably a good idea and is well documented by many reliable sources.  However, there are quite a few cases where an index on a foreign key column probably is not useful.  

Assume your database has a series of tables with standard "auditing" columns (CreateUsrID, CreateDate, LastUpdateUsrID, LastUpdateDate) where the UsrID columns refer back to a Usr table (UsrID).  

This is a common design pattern.  In most cases I've seen foreign keys created on CreateUsrID and LastUpdateUsrID that refer back to Usr(UsrID).  DRI in this case is probably a good idea.  But are indexes on CreateUsrId and LastUpdateUsrID really needed?  Indexes probably won't be needed in this design if:

  1. You rarely fetch data from the tables by searching for rows Created or LastUpdated by a given UsrId.  In other words, you never fetch data by searching for the parent key rows and then joining to get the child rows.  
  2. You rarely DELETE or UPDATE a UsrID from the parent table.  In other words, you do not benefit from having cascading DRI.  In our auditing example, if you rarely update all of UsrID 12's rows to UsrID 14 (change the row ownership from Mike to Bob) then an index on the foreign key column again won't be needed.  

So there you have it.  If you aren't traversing your model from parent to child rows or using cascading DRI, then an index on a foreign key column probably won't help you.