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

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 Received on Wed Apr 24 2002 - 18:49:28 CDT