Re: How to change a foreign key value ???

From: vertis <vertis_at_xs1.xs4all.nl>
Date: 1995/08/09
Message-ID: <409pj8$c06_at_news.xs4all.nl>#1/1


Stefano UNTERNAEHRER (unter_at_mecati.mecasoft.ch) wrote:

: Suppose I have 2 simple tables created with:
 

: create table product (
: pkey number(4) constraint product_pkey_nn not null,
: descr varchar2(30) constraint product_descr_nn not null,
: family number(2),
: constraint product_pk primary key (pkey) ....,
: constraint product_family_fk foreign key (family) references fam,
: ....,
: )
: ....;
 

: create table model (
: mkey number(6) constraint model_mkey_nn not null,
: prod number(4) constraint model_prod_nn not null,
: price number(8,2) constraint model_price_nn not null,
: author number(2) constraint model_auther_nn not null,
: autdate date constraint model_autdate_nn not null,
: constraint model_pk primary key (mkey) ....,
: (*) constraint model_prod_fk foreign key (prod) references product,
: constraint model_author_fk foreign key (author) references emp,
: ....,
: )
: ....;

: All works fine.... A certain day we have to change one product's pkey.
: Not a current operation, but it can arrive due to reorganization....
: Now, with Oracle7 I will have this error:
 

: ORA-02292: integrity constraint (MECAORA.MODEL_PROD_FK) violated -
: child record found
 

: This error is due for the constraint signed with (*), of course, and
: I'm happy to see that constraints finally works with version 7 :-)))
: What to do to avoid this problem and, even better let automagically
: change childs records' value as well ?

How about:

insert into product
select <new_pkey>

,      descr
,      family 

from product ;
/* copy record */

update model
set model_prod_fk = <new_pkey>
where model_prod_fk = <old_pkey> ;
/* relocate children */

delete from product
where pkey = <old_pkey> ;

commit ;

Hope this helps...

Teijo Doornkamp (doornkampt_at_vertis.nl) Received on Wed Aug 09 1995 - 00:00:00 CEST

Original text of this message