This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes EJB and other Java EE Technologies and the fly likes Referential integrity error in Child table even when Parent table has values Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Java » EJB and other Java EE Technologies
Bookmark "Referential integrity error in Child table even when Parent table has values" Watch "Referential integrity error in Child table even when Parent table has values" New topic
Author

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

Jay Kris
Greenhorn

Joined: Dec 15, 2010
Posts: 2
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
Greenhorn

Joined: Feb 27, 2004
Posts: 29
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
Greenhorn

Joined: Dec 21, 2010
Posts: 2
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
Greenhorn

Joined: Dec 21, 2010
Posts: 2
If someone has answer to this, please help me..
 
It is sorta covered in the JavaRanch Style Guide.
 
subject: Referential integrity error in Child table even when Parent table has values
 
Similar Threads
forgein keys
EJB and SQL two phase commit problem
Unable to create many-to-one mapping
problem while inserting values
Hibernate Bidirectional Mapping Problem.