Referential integrity error in Child table even when Parent table has values

[Jay Kris]

Following is the issue I am facing.

Entity Table 1 - Parent table
Fields: Id - Primary key - autogenerated. Foreign key to Table 2
Name

Entity Table 2 - child table
Fields: Id - Primary key
Status - Primary key

The values for Table 1 are inserted in a previous transaction. The data is persisted in db and is available to other flows.

To insert in Table 2 following are done:
1) Fetch the Id from Table 1 using a JPA query and populate it in the Entity object of Table 1.
2) Populate the Entity object for Table 2 with Id fecthed in Step 1 and the Status value.
3) Persist the Entity objetc in Step 2 using EntityManager.persist()

During the third step the below error occurred:
org.apache.openjpa.lib.jdbc.ReportingSQLException: Missing key in referenced table for referential constraint (informix.table2_f01). {prepstmnt 1086341312 INSERT INTO table2 (id, status) VALUES (?, ?)[params=(int) 123, (short) 5]} [code=-691, state=23000]

The constraint refers to the referential integrity between Table 1 and Table 2 on the Id field.
When the same process was repeated a few seconds later, there was no error.

There is no possibility that the value entered for Id column ie. 123 is not present in Table 1.

Additional info:
Using Informix db
The insert process in Table2 is asynchronous

Any ideas on why the above error may have occured the first time? Do we need to add something to persistence.xml or can it be caused if the parent table is write locked by some other process?

[Oliver Chua]

Hi Jay,

Posting the Entity and the code that does the insert might help us with troubleshooting the issue.
Also, have you tried retrieving Entity1 using em.find(Entity1.class, id) to see if it can see the Entity1 instance?

[Mahesh Balasubramanian]

I also faced the same issue. Knowing fully that a master record exists in my Trainer table (PK table), I tried inserting record into the Events table (FK table). Now, the relation of Trainer-Events is 1-many.

The Trainer PK is an integer. In my events remote interface, I declared setTrainerId() as:
void setTrainerId(Integer trainerId) throws RemoteException, CreateException;
...
However, in the bean, I declared setTrainerId() as:
public abstract void setTrainerId(int trainerId) {
...

The difference is in param type (Integer Vs int).

I am using 1.5 version as my underlying JDK, which allowed autoboxing, hence didn't complain during deployment.
But it resulted in referential integrity exception from the client program of events EJB (saying child record cannot be added/updated due to non-existing trainer).

When I changed the bean setTrainerId() as:
public abstract void setTrainerId(Integer trainerId) {
...

.. it worked fine. I still don't know the reason why it worked now. As per my understanding, primitive types like int are serializable, hence an int as the param type in the bean should work as well.

Can someone please tell me the reason?

[Mahesh Balasubramanian]

If someone has answer to this, please help me..