This article triggers a modeling thoughts in me.
Lets categorize some FK relationships first:
1. Collections such as order-lines in an order, or class members in a class.
Objectively we can say these are relationships where child's have equal lifespan as the parent.
2. References to locations or other static entities.
Objectively we can say these are relationships where child's have less or equal lifespan as the parent.
3. Contained references to entities that are closely related (point to a primary contact that is found in a collection of contacts).
Objectively we can say these are relationships in which a child is not part of a collection, but does reference one that is related! Lifespans are less clear here but they are typically correlated!
I usually model these typical parent-child relations in a way that the child's first PK field is actually the FK field to the parent. In which case there is never need for a separate index specifically for the FK. I do this to get a leaner indexed model that still does the job. The reasoning behind this is that properly knowing the ID of a child record implies knowing its context (the parent record ID).
The selectivity of the 1st PK field (used for statistics), might be a bit lower when there exist many childs per parent, but for most applications this will not be the case. And if one only knows the Parent ID, this modelling makes finding the child records is the least IO intensive as bookmark look-ups are avoided and the data is in order. For mutations, maintaining only the clustered index instead of also an additional FK index is better performing as well reducing possible lock issues.
For pure references it can be or cannot be beneficial to have index on a FK. If I want to get a list of all warehouses that are located in England, then obviously an index here is beneficial. But most likely I will not need this location context to do something with a specific warehouse and thus is will not become part of the primary key!
I will never delete a country just like that and have the delete cascade, so this case also will not demand in index on a foreign key.
Assume we have a warehouse and related to that we have various collections for this warehouse (orders, contacts, etc.). These collections are likely to be modified and maybe even deleted.
Lets see how we deal with this for orders and contacts:
Lets assume orders are collections themselves here. Orders are unlikely be deleted, but merely marked as such by means of a status change! As such I would argue here the warehouse ID does not need to be part of the order PK, but an index on the warehouse FK is desired. It allows for a quick overview of all orders per warehouse and does not "burden" order-lines with the warehouse PK. And it is highly unlikely we ever need to ask the server to deliver all order-lines per warehouse!
Contacts is likely a list per warehouse and as such the warehouse is the "parent" in a collection. I would certainly have the warehouse ID as the first field in the contact PK (see collections). Now if we want to designate a primary contact for a warehouse and we know it will always be just one, it makes sense to do this on the warehouse level. This will form a reference into a child collection.
If we model the child collection PK as <Warehouse PK>+<Contact PK> then the reference into this child collection is automatically indexed trough the clustered index on warehouse!
By thoughtful modelling the need for indexes on foreign keys can be vastly reduced! In these examples there are just two. And with respect to collections just one because of a chain of collections (warehouses containing orders, containing order-lines). The longer a chain and/or the more such chains, the more indexes on foreign keys are required. But the majority can be cut off at the knees in the modeling phase (for the "leaf" collections).
Also consider the impact of indexes, that can be avoided!
1. They take up resources (CPU and storage)
2. They can cause locking issues when changes are applied at busy times.
3. They make the job of the query optimizer harder as more options have to be evaluated.
Indexes are wonderful, but be very selective when applying them!