Optimizing delete for a table referenced by lots of foreign keys

views:

answers:

+4 Q:

Optimizing delete for a table referenced by lots of foreign keys

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?

+3 A:

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

Daniel Vassallo 2010-02-22 08:57:37

Thanks, that answers the question! Mine is probably a duplicate...

Shaul 2010-02-22 09:05:13

+1 A:

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.

anthares 2010-02-22 09:03:08

Actually, it's different still. "Key" just means you can use it as an identifier. An index is an access structure that can be conveniently used to *implement* a key. The term 'foreign key' means, "it's a key, but not a key of this table, it's a key of another, 'foreign' table". And typically, when people say 'foreign key' they actually mean 'foreign key constraint', as in, a constraint that enforces the referential integrity of the foreign key.

Roland Bouman 2010-02-22 09:29:57

ansaurus

tags:

views:

answers:

Optimizing delete for a table referenced by lots of foreign keys

related questions