Index Foreign Keys

[dhecht]

Is it always a good idea to index foreign keys or should this be done only when parent rows are deleted? That is, do indexes on foreign keys improve Select, Insert, or Update performance. I've heard 'do it always' and 'only when parent rows often deleted'.

If a general rule is to use indexable predicates, would this also not generally apply to joins.

Thanks.

 

[markmorgan]

Either way is ok. It depends on your deletes and updates...

Though there is obviously a vast overhead of declaring indices for every foreign key.

If a delete occurs on the parent table, then DB2 must check that there are no rows referencing this row via a foreign key, otherwise these constraints will be breached. To identify the breach it must read each table on the foreign key column(s); this will obviously perform a tablespace scan if there is no index present to facilitate this read.

If and update occurs on a unique constraint column (let's assume you will not update a primary key) that is referenced by foreign key(s) - this is classed as a candidate key - then the same as above applies.

Mark.

I have just checked. One of my main tables is referenced via foreign keys from 94 others. Thankfully you cannot delete them, just give them an end date.