I know that foreign keys should be indexed, but I can't find the reason why or , at least, why do some DBMS have it automatically and others don't.
Could someone please help me to clarify this and/or give some examples of DBMS that do/don't create indexes for fks?
Thank you.
Every update or delete on the referenced column requires the database to check all the referencing columns for integrity violations. Each of those checks requires checking the referencing columns to see if they're using the value that is about to be changed or deleted.
So validating the integrity of the FK requires a lot of queries on the columns that reference it. Adding indexes to those columns makes the integrity checking much quicker. If there weren't indexes on the referencing columns, then changing the referenced column (or deleting a row that has FKs pointing to it) would require doing table scans on every table that that references it. Table scans are not your friend but FKs certainly are.
Since foreign keys represent relationships between data and are expected to be leveraged when selecting results, it makes sense to index them to improve the speed of the operation.
Whether or not they are automagically indexed by your db engine is really just an implementation decision.
