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

From: Lee McAllister <avernus_at_iag.net>
Date: 1995/08/10
Message-ID: <40bk0j$6nl_at_news.iag.net>#1/1


unter_at_mecati.mecasoft.ch (Stefano UNTERNAEHRER) 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 ?
 

>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 \
>/________________________) (____________________________\

Hmmm, it is generally not a good idea to have a rows primary key change value. It leads to the sort of problems you have run into. More often than not, when a unique identifier like the one you mention mutates, the best bet is not to use it as the primary key but instead use a non user data key, i.e. a sequence upon which relationships will be built. Hence the user data can change without causing the difficulties you are experiencing.

Hope that helps a little.

LeeM. Received on Thu Aug 10 1995 - 00:00:00 CEST

Original text of this message