Data modeling: (Why) Should I always create indexes on the FK columns?

Question

Consider the following scenario:

ParrentLookupTable:

PK: SomeCode CHAR(10)
has only 5 rows.
Does not change often

MyTable:

One million rows are being added daily.
Has the column SomeCode FK to ParrentLookupTable
There is no query that search or sort based on SomeCode

Here is the FK definition

ALTER TABLE MyTable  CONSTRAINT FK_MyTable_ParrentLookupTable_SomeCode FOREIGN KEY(SomeCode)
REFERENCES ParrentLookupTable (SomeCode)

Should I create an index IX_MyTable_SomeCode?

The index would cost IO during this write intensive workload scenario and I am not sure how it becomes useful?

Then how you are going to maintain integrity? Are you going to create custom code code like trigger to maintain integrity? Or you will have nightly job to manage integrity? — Kannan Kandasamy, Sep 06 '16 at 17:47
@KannanKandasamy: Sorry, the FK definition was missing. I updated the question with the FK definition. — Allan Xu, Sep 06 '16 at 19:00
Why do you think you need to add an index on the child table? — rjdevereux, Sep 06 '16 at 19:07
Similar question: https://stackoverflow.com/questions/3650690/should-every-sql-server-foreign-key-have-a-matching-index — rjdevereux, Sep 06 '16 at 19:11

score 1 · Answer 1 · answered Sep 06 '16 at 19:12

In general adding an index on the column in the parent table is to speed up the FK check. In this case it might not help at all. ParentLookupTable will fit into one database page so it should not matter much if it is doing a table scan or a index seek.

Adding an index on the FK column on the child table could help in the case of cascading deletes.

Here is a longer article on the subject http://sqlperformance.com/2012/11/t-sql-queries/benefits-indexing-foreign-keys

Data modeling: (Why) Should I always create indexes on the FK columns?

1 Answers1