ORA-02291: Parent key not found

User e4183f4538

29-11-2010 20:50:06

Hi!


I am fumbling my way through Oracle and integration with IJC. As of this morning, I could not log into my previously working schema. When logging into IJC with Oracle authentication (as the schema owner, with all the previously working IJC* tables), I am pitched the following error:


 


 




 


java.sql.SQLException: ORA-02291: integrity constraint (CHEMAXON.FK_IJC_ITEM_USER_ITEM) violated - parent key not found


[java business removed]


Caused: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO IJC_ITEM_USER (SCHEMA_ID, ITEM_ID, USERNAME, TYPE, CREATION_TIME, INFO) VALUES (?, ?, ?, ?, SYSDATE, ?)]; ORA-02291: integrity constraint (CHEMAXON.FK_IJC_ITEM_USER_ITEM) violated - parent key not found


; nested exception is java.sql.SQLException: ORA-02291: integrity constraint (CHEMAXON.FK_IJC_ITEM_USER_ITEM) violated - parent key not found


 




 


From what I can tell, IJC is trying to insert into the IJC_ITEM_USER table, which has the foreign key FK_IJC_ITEM_USER_ITEM. This key's referenced table and columns are IJC_SCHEMA columns SCHEMA_ID and ITEM_ID. In order to insert into IJC_ITEM_USER, the values must exist in IJC_SCHEMA columns SCHEMA_ID and ITEM_ID (correct?). Apparently whatever it's trying to reference to with the insertion of a row in IJC_ITEM_USER doesn't exist in IJC_SCHEMA, which contains 64 rows currently. 


It worked dandy the last time I was logged in and handling data (post-Cartridge install), and I haven't done anything in the meantime.


Any ideas? Or any ideas on how to troubleshoot this better?


Thanks!


 

ChemAxon fa971619eb

30-11-2010 09:45:39

Hi Erin,


This error is usually because you are trying to connect to an IJC schema that no longer exists. The error message is a bit misleading (we are going to fix this), but what it normally means is that you set up a connection to the DB in IJC and the ID of the IJC schema is stored in the settings file that is read by IJC. This settings file will be in the .config directory of your project and will have a .ijs extension.


Then the schema with that ID was deleted from the database (e.g. the row for that schema was deleted from the IJC_SCHEMA table).


Then you try to connect to that missing schema and you get the somewhat cryptic error.


To verify this check the ID of the schema in the settings in IJC and check whether this exists in the IJC_SCHEMA table in the database.


The solution is probably to delete the offending schema from IJC and use the 'Connect to schema...' wizard to connect to the correct one again.


Tim 

User e4183f4538

30-11-2010 18:58:44

Tim,


That was indeed the problem - many thanks!


For future reference, is there a consensus on why this happens? IJC was looking for schema Exxxxxxxxxxx, but there were only Bxxxxxxxxxxx records in IJC_SCHEMA. 

ChemAxon fa971619eb

01-12-2010 10:07:21

No obvious reason other than user deleted IJC schema in DB, either deliberately or by mistake.


There is possible route to confusion as the distinction between 'New schema' and 'Connect to schema' wizards is not particularly clear. 'New schema' adds a new IJC schema, while 'Connect to schema' connects to one that has already been created. This might explain why multiple ones were created, though not why one was deleted.


We plan to remove this potential confusion soon, but in the meantime make sure you choose the right one.


Tim