Re: FK - automatic value change when parent changes
Date: Fri, 26 Feb 2010 08:59:37 -0800 (PST)
Message-ID: <9fa0cb07-904e-421f-b265-d0e5ff234981_at_g28g2000prb.googlegroups.com>
On Feb 26, 6:11 am, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> 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 --
I had a situation where people were preparing reports for auditors, and noticed some invoices had dropped off reports. Eventually I discovered some customers had moved, and changed their addresses. This system allows multiple invoicing and delivery addresses, so there is by convention addresses with DEFAULT as part of an address code compound key. The actual address is not part of the key, because obviously, businesses move all the time. So doesn't it make perfect sense to keep the DEFAULT row and change the address? Well, not if you are running tax reports for auditors that look at the state in the address row. Obviously, the state (or should I say, address state state) should have been kept at the time of invoice. But golly gosh gee, wouldn't that be violating normal form as you replicate these foreign keyed rows? Perhaps the design problem was in creating something called default and expecting users to realize they need a new address code that isn't called default and change the code to not default to default... I actually had to do another involved project to keep the invoice as printed just to be able to reprint them, things like "this part backordered" are dynamic. Problem with classic relational theory, it lacks time dynamism, it's not always just another attribute as you discover unelocuted requirements.
jg
-- _at_home.com is bogus. "After tweeting new paper, 193 downloads in 20 days. After blogging it, 209 downloads in 20 hours." - Cary Millsap (tweeted)Received on Fri Feb 26 2010 - 10:59:37 CST