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

Stefano UNTERNAEHRER wrote at Mon, 31 Jul 1995 19:51:41 GMT
>
>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 ?
>
>I mean, is there a way to let 'cascade' the update command in others
>tables having this table.column as foreign key ?
>
>When not, have I really to permit the end user to:
>1- disable all the constraints having this table.column as fk
> (alter table model disable constraint model_prod_fk);
>2- update the column's value in all the tables
>3- enable all the constraints
> (alter table model enable constraint model_prod_fk);
>4- praying all will work fine during this time
>5- crying when not ? :-)))
>Note that to permit this sequence, the end user must be granted to
>a role having the 'alter any table' privilege :-(((
>
>Hope to read a better way to do that ...

Sorry, Oracle hasn't cascade update.

I believe, that a better way is a

1- insert in a PRODUCT new record with "foo" key value.
2- update prod in all records at MODEL, related to old pkey to "foo" value.
3- update pkey in PRODUCT.
4- update all records in MODEL with "foo" pkey to new pkey value.
4- delete record with "foo" pkey from PRODUCT.

For more convinience to users you can encapsulate this process into stored procedure.

Alexander Alesinsky,
JSV Investor
Kharkov
Ukraine Received on Fri Aug 18 1995 - 00:00:00 CEST