Re: FK - automatic value change when parent changes

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Fri, 26 Feb 2010 06:11:38 -0800 (PST)
Message-ID: <74fcaedd-83f1-49ab-af28-3c9452bb2623_at_t23g2000yqt.googlegroups.com>



On Feb 26, 4:56 am, Shakespeare <what..._at_xs4all.nl> wrote:
> Op 26-2-2010 9:36, eh schreef:
>
> > Hi,
> > we have a Fk pointing to a parent table/column - and now sadly enough
> > that primary key value will change.
> > What would be a smart way to implement this change to these child
> > tables ?
>
> > Disabling FK's and a lot of update clauses or .... ??
>
> > BR,
> > timo
>
> If it changes, it's not a primary key...
> Anyway, if there are no other unique keys on the master table, do not
> change the key, but copy the record with a new pk, restore child
> connections by updating the foreign keys and delete or archive the
> original master record. Note that while performing these actions,
> queries may give wrong results (sum, count, etc).
>
> Shakespeare

Shakespeare has given the correct sequence for the process where it is necessary to update a PK to a new value. I will agree that as a general rule a PK should not change; however, in the real world it is sometimes necessary to change a PK which is why the ANSI standard requires that PK in fact be updatable. Mergers and acquisition activity often leads to the need to re-key information where the two merged parties had overlap in vendors or customers.

But if you find it necessary to update a PK value which in turn requires updating related data on a regular basis then you do need to re-examine the choice of PK.

HTH -- Mark D Powell -- Received on Fri Feb 26 2010 - 08:11:38 CST

Original text of this message