Foreign Keys – to index or not

In SQL Server, a Primary Key (PK) is by definition a Unique Index. Many times I find that developers have added the extra overhead of creating a separate index.

In contrast, Foreign Keys (FK) are constraint and are not required to be indexed.

Indexes are to be considered as mini-tables in their own right. The each insert on the table, there must be an insert on every index too. Likewise for both updates & deletes. So overuse of indexes will create further issues.

However, in some circumstances, the lack of an index on the FK can bring an application to it’s knees. Recently, I came across one of these siutations on an Oracle database and decided that it was time to blog.

In this scenario, a core document table was created. It contained eight reference tables with PKs & FKs set up as appropriate.

Here we discovered that the entire table would lock up when particular deletes were carried out.

If a document is inserted or updated, each FK field is verified against the PK of the reference table. The index of the PK makes this exercise very simple.

If a document is deleted, no checks need to be made at all.

This can be done easily without any index on the foreign keys.

However, consider the implications of a change to one of the reference tables:

  • an insert has no impact as there should be no FK on a PK value that does not yet exist.
  • an update to any field except the PK causes no impact to the document (FK) table
  • however, a delete (or update to the PK value) requires the database system to check that no document (FK table) record uses this value.

This last item is the core problem.

If there is an index, then dependent records can be quickly found with little or no locking. And it has even less impact, if no dependent record is found.

But if there is no index, the the database system must scan the entire table to ensure that there are no dependent records and all locks obtained must be retained for the duration of the table scan. On a sizable table, this can take some time.

A small number of deletes rarely exposes the problem. However, as deletes increase, this scaleability issue becomes more apparent.

So, an index on the FK of the main table is necessary if the reference table record might be deleted, or it’s PK is altered, during the production day.

Otherwise, whether deletes & amends are done out of hours, or not done at all, this index requirement can be avoided.

In our example, we identified 2 FKs on the document table that needed to be added but the other 6 FK would never need an index in normal usage. Given the size of the document table, significant performance impacts associated with index management were avoided.

Leave a comment

1 Comment

  1. >Is it reasonable to assume that if the FK is not in the result set of a query on the dm_db_missing_index* views that I shouldn't bother creating an index on the FK?


Leave a Reply

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

You are commenting using your 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: