Optimizing delete for a table referenced by lots of foreign keys

Question

I have a table Document that is referenced by a ton of other tables via foreign keys. I am trying to delete a Document record, and according to my execution plan, SQL Server is doing a clustered index scan on every one of the referencing tables. This is very painful.

I thought having a FK automatically made an index on the FK fields? Or am I wrong? Do I really have to go around my database putting an explicit index on every single FK field?

Answer 1

No, SQL Server does not create an index automatically when you create a foreign key. You have to create the indexes yourself.

Related post: Does a foreign key automatically create an index?

Answer 2

The name "Foreign key" is a little misleading because, it is usually associated with "primary key", which is indexed automatically. So, people may presume that foreign key is indexed, too.

The deal is that "key" here refers to a constraint. So with adding a foreign key you actually add a constraint. Index is a totally different deal.