Re: Foreign key / performance question

On Wed, 29 Mar 2006, Nico Callewaert wrote:

> Is it wise to define foreign keys for referential entegrity ?

> Example : I have a customer table with 40 fields. Out of that 40
> fields, 10 fields contain information linked to other tables. So, is
> defining foreign keys for these 10 fields a good idea ? Because from
> what I understand, for every foreign key, there is an index defined.
> So, all these indexes has to be maintained. Is that killing
> performance ? What's the best practise : defining foreign keys or not
> ?

The referencing side of the constraint doesn't need an index, although
it's useful for speeding up deletes or updates to the referenced table
(so, if those operations don't happen or are significantly rare, having
those have to do a sequential scan may be better than the maintenance
cost of the index on the referencing side). The referenced side does need
an index, however that's theoretically the same index that's used to
guarantee the required unique/primary key constraint.