Looking at DB tables created by a different developer I have noticed that whenever a table had a forein_key_id field/column, it was always an INDEX/KEY.

I am not sure if it was manually created, or automatically by some 3rd party software. However, I myself usually created keys using different principles. i.e. Just because it is a foreign key, I don't use that fact to also make it an INDEX. I would typically look at JOIN considerations and JOIN performance individually for a particular query first.

So I am curious to find out more about this -- do foreign keys have to be made into an INDEX? If yes, what is the rationale behind it?

  • Most, if not all, RDBMS automatically create an index on a FK because a FK means you will be doing joins using those columns.

  • It's standard RDBMS behavior based on the fact that you will be using those columns in searches.

  • A query with a join in it, even when absent a WHERE clause, is doing searches on the joined columns and comparing then with the PK of another table-

  • RDBMSs like to speed up joins. So they create an index on FK.

  • The optimizer, based on statistics, or based on access costs will decide whether or not to use such an index when executing a query.

  • Thanks. One of my concerns was that i.e. I have 5-6 foreign keys, the table will have 5-6 indices. All of them are updated on each INSERT. If I only use 3 indices 90% of the time, it may be more beneficial to tweak or DROP the indices that are not being utilized. It's a performance/clutter consideration on my side – Dennis Aug 22 '16 at 21:00
  • 5
    Dropping the indexes also will slow down inserts, updates and deletes because then the engine will have to check referential integrity by sequentially scanning instead of index scanning or index look up. If you have 6 FK in a table that means it shouldn't be a transactional one but some kind of historical summary table. Maybe you should normalize a little bit more. – Tulains Córdova Aug 22 '16 at 21:08
  • @Dennis Remember to upvote. – Tulains Córdova Aug 22 '16 at 23:16
  • 10
    It is most certainly not standard RDBMS behavior. Oracle, PostgreSQL, SQL Server and Sybase don't, and I'm pretty sure DB2 doesn't, either. There are valid reasons not to index a foreign key column, and MySQL is the only RDBMS I know of that forces it. – Blrfl Aug 24 '16 at 13:56
  • SQLite also doesn't create index on a FK column automatically. Most of the sentences in this answer are false. – iwis Apr 30 at 17:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.