Re: FK - automatic value change when parent changes
Date: Sat, 27 Feb 2010 08:46:25 -0000
Message-ID: <DYydnSibedd9RhXWnZ2dnUVZ8tSdnZ2d_at_bt.com>
"eh" <timo.talja.email_at_gmail.com> wrote in message news:5b872e3d-26fc-4509-b079-31a7da73dde1_at_g28g2000yqh.googlegroups.com...
> 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
As pointed out by Mark Powell, relational theory does allow primary keys to change; but there is a feeling in the market place that it's not supposed to happen - and you don't see it happening in many systems.
In Oracle implementations, you need to create the foreign key constraint as "deferrable initially immediate". Then your code goes something like this:
alter constraint {fk constraint} deferred;
update rows in child table to match not-yet-existing primary key;
update key of parent table;
alter constraint {fk constraint} immediate;
If you've made a mistake at this point Oracle raises one of several errors (but I can't remember all the variations and numbers - the obvious set of experiments will identify them).
You can examine the error and decide on what action to take - probably rolling back. It's probably best to do one PK at a time as this makes it easier to work out what's gone wrong.
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.comReceived on Sat Feb 27 2010 - 02:46:25 CST