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