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

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

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp