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?
Best Answer
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.