Re: HELP! Oracle Foreign Key Error When Updating Parent Non-PK Field

Thanks for the quick response Mr. Kyte.

There IS such a trigger on the parent table (i.e. t1), and it was accidentally coded as BEFORE INSERT OR UPDATE, instead of BEFORE INSERT. This will cost me an hour of so of recreating triggers (since all of the primary tables use an id field that depends upon this trigger), but it fixes the problem nicely.

Thanks again!

Michael Jones

Thomas Kyte <tkyte_at_oracle.com> wrote in message news:<aa7ji7015tp_at_drn.newsguy.com>...
> In article <5dfe0034.0204241549.77018cca_at_posting.google.com>, szaijan_at_ternea.com
> says...
> >
> >Hi Folks,
> >
> >thanks in advance for any help with this issue...
> >
> >I have a master and a detail table. I am trying to update a
> >non-primary key, non-referenced column in the master table.
> >I get a "child record found" foreign key constraint violation
> >when attempting to update the field. Details follow.
> >Can anyone tell me how to fix this problem without getting
> >rid of the foreign key?
> >
> >The master table:
> >
> >sessions
> > id NUMBER(20),
> > updated DATE,
> > other columns...
> > CONSTRAINT PRIMARY KEY ( id )
> >
> >fns
> > id NUMBER(20),
> > session_id NUMBER(20),
> > other columns...
> > CONSTRAINT PRIMARY KEY ( id ),
> > CONSTRAINT fns_session_id_fk
> > FOREIGN KEY ( session_id )
> > REFERENCES sessions ( id )
> > ON DELETE CASCADE
> >
> >...Note that here is no index on the Foreign Key.
> >
> >I try to do an update on the sessions table with:
> >
> >UPDATE sessions SET updated = SOME_DATE WHERE id = PARENT_ID;
> >
> >There IS a fns record which references the parent record.
> >
> >I get an error as follows:
> >
> >ORA-02292: integrity constraint (ASBEWEB.FNS_SESSION_ID_FK)
> > violated - child record found.
> >
> >Michael Jones
> >szaijan_at_ternea.com
>
>
> Look for a trigger on your parent table that causes side effects (eg: a before
> insert/update trigger that sets the ID column to a sequence.nextval or
> something...)
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t1 ( id int primary key, updated
> date );
>
> Table created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t2 ( id int, constraint t2_fk
> foreign key (id) references t1 );
>
> Table created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t1 values ( 1, sysdate );
>
> 1 row created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t2 values ( 1 );
>
> 1 row created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create or replace trigger t1_trigger
> 2 before update on t1
> 3 for each row
> 4 begin
> 5 :new.id := 2;
> 6 end;
> 7 /
>
> Trigger created.
>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
> ops$tkyte_at_ORA817DEV.US.ORACLE.COM> update t1 set updated = sysdate where id = 1;
> update t1 set updated = sysdate where id = 1
> *
> ERROR at line 1:
> ORA-02292: integrity constraint (OPS$TKYTE.T2_FK) violated - child record found
Received on Thu Apr 25 2002 - 14:31:14 CDT