How to change a foreign key value ???

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 ... Thank you,
Stefano

______________________|    Stefano Unternaehrer    |__________________________
\  Mecasoft SA        |         Oracle DBA         | fax:    +41 93 335 507  /
 \ 6600 Muralto       |     and  PL/SQL, Pro*C     | voice:  +41 93 337 444 /
  \                   |     C, XWindow & Motif     |                       /
  /                   |     Software Developer     | email:                \
 / Switzerland Europe |____________________________|  dba,unter_at_mecasoft.ch \
/________________________)                      (____________________________\