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.

Irish Windows Vista Launch with Neil Armstrong

Today, I attended the Windows Vista launch (plus Office 2007 & Exchange 2007) at Croke Park. I felt like an uber geek – at 40, visiting Croke Park for the first time, for a software launch – never having been to a match or a concert there. Oh well!

The launch was impressive. We will all get complimentary copies of Windows Vista & Office 2007. I had already received SQL Server 2005 & Visual Studio 2005 in November, 2005 at their launch. Well worth the day off work!

The highlight was Neil Armstrong and he was impressive. He humbled us with his knowledge of Irish explorers & scientists throughout the ages and the impact of their work on his own achievements & well-being in space. He weaved a message on the need to take the next step & persevere in your goals. One can evaluate the risks, & offset them as best as possible, but one must be prepared to take that step into the unknown. Success cannot be achieved without determination.

It is hard to believe that this man walked on the moon a month before my third birthday. I was assured by my parents that I saw him on the day but I sure cannot remember it now!

His talk was inspirational and helped me get into the mindset in advance of an interview for a competitive promotion. He well deserved the standing ovation. Thank you, Neil.

%d bloggers like this: