should I make an index on foreign key in sql server tables?

Question

Hi every body

I have read and search many topics and each topics and user say's their opinion about indexs.

my question is very simple. I have many tables and some of them has a relationshiop with each other with foreign key.:

example :

Table Line :

LineId int Primary-key (clustered idnex)

LienNo

,.....

Table Joint :

JoitnId int Primary-key (clustered idnex)

LineId foreign key for realtion with Line table.

now should I make a LineId foreign key in joint table or leave it alone?

Answer 1

It's often a good idea to create an index foreign key columns. This will avoid a full table scan to check referential integrity when rows are deleted form the referenced Line table and may improve performance of joins between the two tables on LineId.

I'll add that you may be able to avoid the separate foreign key index with an identifying relationship (assuming a Joint row cannot exist without a Line row), where the Joint table PK is a composite key on LineId and JointId. This will help performance of joins between the tables but will not guarantee JointId alone is unique nor help optimize queries on JointId alone.

Answer 2

As Dan says, it is often a good idea to put an index on FK column. But often is not always. I have a database next to me with plentiful of unindexed FK columns. As Dan says, it helps when you are deleting something. But take a lookup table like Currenices. How often to you delete a currency? And in your Transactions table how selective is the CurrencyCode column?