Re: How to change a foreign key value ???
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