Re: FK - automatic value change when parent changes
Date: Fri, 26 Feb 2010 06:11:38 -0800 (PST)
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 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