Re: update pk referenced by fk

In article <D4HJ83.6r8_at_cbr.dit.csiro.au>, robertp_at_hamal.csis.dit.csiro.au (Robert Andrew Power) writes:
> 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
> +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
>
>

Robert,

    In your case, since you want to change PK's, I think your best     option would be to not use PK and FK constraints at all, but     use an update trigger on the parent table to cascade the changes     down to the children. This will maintain your referential     integrity condition without using constraints.

Larry L. Lester
McDonnell Douglas Space Systems
Kennedy Space Center, Florida Received on Fri Feb 24 1995 - 18:43:33 CET