If a Column is a Foreign Key does it need an Index?

Question

I have created indexes on many of my columns in my tables. Many of these columns are foreign keys as well. Can I safely delete the indexes on the columns that are foreign keys?

Answer 1

SQL does not automatically create indexes on foreign key columns, so those indexes are not redundant. Only drop them if you're 100% sure that they are not used.

Answer 2

Don't do that. The whole point of the index is that it joins to another table, so it will almost always factor in queries and always factor in queries that join the table it's linked to.

What possible reason could there be for keeping the foreign key and removing the index from it?

Answer 3

No. Unlike Primary key, Foreign key does not imply an index. An explicit index on a foreign key is useful because foreign keys are often used in joins and the index can speed up the join.

You should look up sys.dm_db_index_usage_stats after a full coverage test (or after some lengthy amount of day-to-day use) to understand what indexes are understand what indexes are ignored by the query optimizer when considering your access paths. These stats are reset after a server restart so they are no relevant unless the application(s) had run for enough time or the tests have covered every query run on the data, with realistic data amounts.

Answer 4

See also this question: http://stackoverflow.com/questions/1196561/does-specifying-a-foreign-key-make-it-an-index/

Answer 5

Normally you would have an index on an FK column. Created manually of course.

However, with no update or delete on the parent table then you probably don't need it. But, unless it's a bottleneck then why? SQL has to check the relationship at some point and the index is pretty much essential then for anything more than a few dozen rows..

We have dropped indexes on some FK columns for a large-ish table deliberately (insert of 5 million rows per day) to manage data space growth. Not performance. Our sole case, otherwise we always have them unless 110% proven otherwise.

Answer 6

It depends. There are some queries that an index on a foreign key will speed up. Updates take longer, of course, because the DBMS has to update the index as well as the table. sometimes it's worth it.

Take a look at the merge join strategy. If your optimizer uses this strategy, it can do massive joins between the table in question and the parent table with just two index scans. Depending on the table population, this can take a fraction of the time required for a loop join strategy.