update pk referenced by fk

From: Robert Andrew Power <robertp_at_hamal.csis.dit.csiro.au>
Date: Fri, 24 Feb 1995 03:33:35 GMT
Message-ID: <D4HJ83.6r8_at_cbr.dit.csiro.au>


When using a foreign key is there any way of updating the parent key such that the child key is updated as well? When I try this I get a :

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

The error makes sense, but I would like Oracle to update the child record as well. I can't do this directly as I get the following error:

ORA-02291: integrity constraint (...) violated - parent key not found

Which also makes sense (unfortunately).

So then I thought I'd use a trigger to do the update for me, but this hasn't helped. Anyone with some ideas???

The following may help illustrate my problem:

create table parent (id char(10) constraint pk_parent_id primary key);  

create table child (id char(10) constraint fk_child_id

                                references parent(id) on delete cascade);
 
insert into parent values ('0');
insert into parent values ('1');

insert into parent values ('2');
insert into parent values ('3');  

insert into child values ('0');
insert into child values ('1');         

update parent set id = '0_new' where id = '0';  

update child set id = '0_new' where id = '0';  

create trigger parent_id_update before update of id on parent for each row
begin

        update child set id = :new.id where id = :old.id; end;
/  

update parent set id = '0_new' where id = '0';

Robert Power

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
robert.power_at_csis.dit.csiro.au
CSIRO Division of Information Technology

PO Box 664 					tel: +61 6 275 0960
Canberra ACT 2601 AUSTRALIA.			fax: +61 6 257 1052

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Received on Fri Feb 24 1995 - 04:33:35 CET

Original text of this message