Optimizing delete for a table referenced by lots of foreign keys
foreign-keys, sql-server, sql-server-2005
Solution
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?
Problem
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?