Re: FK - automatic value change when parent changes
From: Shakespeare <whatsin_at_xs4all.nl>
Date: Fri, 26 Feb 2010 15:35:33 +0100
Message-ID: <4b87dc2d$0$22936$e4fe514c_at_news.xs4all.nl>
Op 26-2-2010 15:11, Mark D Powell schreef:
> 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 --
>
>
Date: Fri, 26 Feb 2010 15:35:33 +0100
Message-ID: <4b87dc2d$0$22936$e4fe514c_at_news.xs4all.nl>
Op 26-2-2010 15:11, Mark D Powell schreef:
> 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 --
>
>
That is why some standards propagate technical keys, but they can make building applications a crime, and even then mergers etc will possibly create problems, since most technical keys are numbers....
Shakespeare Received on Fri Feb 26 2010 - 08:35:33 CST