It is quite customary to systematically index the foreign keys of a table; and it is widely acknowledged to be common wisdom to do so. In fact, some design tools automatically generate indexes on these keys, and so do some DBMS. However, I urge caution in this respect. Given the overall cost of indexes, unnecessarily indexing foreign keys may prove a mistake, especially for a table that has many foreign keys.
Of course, if your DBMS automatically indexes foreign keys, then you have no choice in the matter. You will have to resign yourself to potentially incurring unnecessary index overhead.
The rule of indexing the foreign keys comes from what happens when
(for example) a foreign key in table
A references the primary key in table
B, and then both tables are concurrently
modified. The simple model in Figure 3-7 illustrates this
Figure 3-7. The simple, Master-Detail example
Imagine that table
A is very
large. If user U1 wants to remove a row from table
B, since the primary key for
B is referenced by a foreign key in
A, the DBMS must check that removal of the row
will not lead to inconsistencies in the intertable dependencies, and
must therefore see whether there is any child row in
A referencing the row about to be deleted from
B. If there does happen to be a row
A that references our row in
B, then the deletion must fail, because otherwise we would ...