Re: Indexes and Foreign Keys

In a well designed system the PK value of any table should almost never change. But in those rare instances where the PK value needs to be changed the ANSI standard supports doing so. Depending on the FK definition you are likely to have to insert the new PK value, update all FK with the old PK value to point to the new PK value, and then delete the old PK value.

If the PK value has a fair chance of in fact having to change over the life of the data then this is a situation where a surrogate key should be considered. In the last decade on a 1400 plus table application built on natural keys I have had to change maybe one PK. As such I do not think surrogate keys are necessary to prevent this situation.

If any of the following is true then go ahead and be conservative by creating an index to support the FK:
.. the parent table is definitely subject to deletes
.. you have no idea how the tables are updated
.. the application is used differently by different customers
.. the design is not properly normalized to begin with

Just understand that some of those indexes will never be used to access data or support the FK constraint for DML on the parent.

HTH -- Mark D Powell -- Received on Sun Mar 12 2006 - 10:27:45 CST