Quantcast

Constraint violation using OneToOne relationship

classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Constraint violation using OneToOne relationship

mwalter
We have an entity A having a OneToOne unidirectional relationship to entity B configured like this:

In entity A:
@OneToOne(cascade = CascadeType.ALL)
@JoinColumn(name = "B_ID")
private B b;

Now we create new entities A and B and would like to persist them:

final A a = new A();
final B b = new B();
a.setB(b);

em.persist(a);

We get a PersistenceException because OpenJPA tries to insert the entity with the foreign key first:

<openjpa-1.2.3-SNAPSHOT-r422266:1053401 nonfatal general error> org.apache.openjpa.persistence.PersistenceException: ORA-02291: integrity constraint violated - parent key not found
 {prepstmnt 1708156368
INSERT INTO A (ID, ROW_ERF_TSTAMP, ROW_ERF_USER,  ROW_MUT_VERSION, B_ID)
    VALUES (?, ?, ?, ?, ?)
[params=(long) 396, (Timestamp) 2011-11-09 15:38:12.048, (String) TEST, (int) 1, (long) 2772]} [code=2291, state=23000]

Why does OpenJPA first try to insert entity A and not B? An how can we force OpenJPA to execute statements in the right order?

Interestingly this works on our OneToMany relationships.

In the internet I found this and this. But these issues are for OneToMany relationships. IBM provides a solution using the OpenJPA property openjpa.jdbc.SchemaFactory. But we would like to understand why persisting does not work in the OneToOne case above. So has somebody an explanation for our issue? Thank you in advance!

We use WebSphere server 7.0.0.19 and OpenJPA 1.2.3.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Constraint violation using OneToOne relationship

Rick Curtis
What do you have set for properties in your persistence.xml ?

Thanks,
Rick

On Wed, Nov 9, 2011 at 8:57 AM, M. Walter <[hidden email]> wrote:

> We have an entity A having a OneToOne unidirectional relationship to
> entity B
> configured like this:
>
> In entity A:
> @OneToOne(cascade = CascadeType.ALL)
> @JoinColumn(name = "B_ID")
> private B b;
>
> Now we create new entities A and B and would like to persist them:
>
> final A a = new A();
> final B b = new B();
> a.setB(b);
>
> em.persist(a);
>
> We get a PersistenceException because OpenJPA tries to insert the entity
> with the foreign key first:
>
> <openjpa-1.2.3-SNAPSHOT-r422266:1053401 nonfatal general error>
> org.apache.openjpa.persistence.PersistenceException: ORA-02291: integrity
> constraint violated - parent key not found
>  {prepstmnt 1708156368
> INSERT INTO A (ID, ROW_ERF_TSTAMP, ROW_ERF_USER,  ROW_MUT_VERSION, B_ID)
>    VALUES (?, ?, ?, ?, ?)
> [params=(long) 396, (Timestamp) 2011-11-09 15:38:12.048, (String) TEST,
> (int) 1, (long) 2772]} [code=2291, state=23000]
>
> Why does OpenJPA first try to insert entity A and not B? An how can we
> force
> OpenJPA to execute statements in the right order?
>
> Interestingly this works on our OneToMany relationships.
>
> In the internet I found
> https://issues.apache.org/jira/browse/OPENJPA-1961
> this  and  http://www-01.ibm.com/support/docview.wss?uid=swg1PK74266 this
> .
> But these issues are for OneToMany relationships. IBM provides a solution
> using the OpenJPA property openjpa.jdbc.SchemaFactory. But we would like to
> understand why persisting does not work in the OneToOne case above. So has
> somebody an explanation for our issue? Thank you in advance!
>
> We use WebSphere server 7.0.0.19 and OpenJPA 1.2.3.
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Constraint-violation-using-OneToOne-relationship-tp6978223p6978223.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>



--
*Rick Curtis*
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Constraint violation using OneToOne relationship

mwalter
Hi Rick,

these are the properties for the unit test PU:

        <properties>
            <property name="openjpa.Log" value="DefaultLevel=INFO, SQL=WARN, JDBC=WARN, Query=TRACE, Schema=ERROR, Runtime=WARN" />
            <property name="openjpa.ConnectionFactoryProperties" value="PrintParameters=true, PrettyPrint=true" />
            <property name="openjpa.LockManager" value="version" />
            <property name="openjpa.jdbc.TransactionIsolation" value="read-committed" />
            <property name="openjpa.DetachState" value="fetch-groups(DetachedStateField=true)" />
        </properties>

And these properties are for production environment:

        <properties>
            <property name="openjpa.ConnectionFactoryProperties" value="PrintParameters=true" />
            <property name="openjpa.LockManager" value="version" />
            <property name="openjpa.jdbc.TransactionIsolation" value="read-committed" />
            <property name="openjpa.DetachState" value="fetch-groups(DetachedStateField=true)" />
        </properties>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Constraint violation using OneToOne relationship

Rick Curtis
Have you tried setting the following property ? It seemed to fix my
recreate of your scenario (on trunk anyway).

<property name="openjpa.jdbcSchemaFactory"
value="native(ForeignKeys=true)"/>

Thanks,
Rick

On Wed, Nov 9, 2011 at 9:23 AM, M. Walter <[hidden email]> wrote:

> Hi Rick,
>
> these are the properties for the unit test PU:
>
>        <properties>
>            <property name="openjpa.Log" value="DefaultLevel=INFO, SQL=WARN,
> JDBC=WARN, Query=TRACE, Schema=ERROR, Runtime=WARN" />
>            <property name="openjpa.ConnectionFactoryProperties"
> value="PrintParameters=true, PrettyPrint=true" />
>            <property name="openjpa.LockManager" value="version" />
>            <property name="openjpa.jdbc.TransactionIsolation"
> value="read-committed" />
>            <property name="openjpa.DetachState"
> value="fetch-groups(DetachedStateField=true)" />
>        </properties>
>
> And these properties are for production environment:
>
>        <properties>
>            <property name="openjpa.ConnectionFactoryProperties"
> value="PrintParameters=true" />
>            <property name="openjpa.LockManager" value="version" />
>            <property name="openjpa.jdbc.TransactionIsolation"
> value="read-committed" />
>            <property name="openjpa.DetachState"
> value="fetch-groups(DetachedStateField=true)" />
>        </properties>
>
> --
>



--
*Rick Curtis*
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Constraint violation using OneToOne relationship

mwalter
Hi Rick,

thank you for the fast responses!

Yes I successfully tried the solution IBM proposed.

But why do I have to add this property just for OneToOne relationships in order to make them work properly? For all other relationships this is not necessary. I really would like to understand this difference.

Are there any side effects for the persistence mappings by adding the openjpa.jdbc.SchemaFactory property to the PU? I'm suspicious of adding properties to get things to work which in my opinion should work without further configuration. I wonder if creating both sides of a OneToOne relationship and persisting them by saving the parent entity is a special case.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Constraint violation using OneToOne relationship

mwalter
This is just unbelievable! As I expected using this property has side effects.

We have boolean fields defined in our database as number(1,0) because Oracle does not know boolean type. Now we get ArgumentExceptions saying that we declare columns that are not compatible with the expected type "bit". These errors never showed up before we added the property "openjpa.jdbc.SchemaFactory".

Now what?

Remove the property and use OneToMany instead of OneToOne relationships with programmatically limit the number of child elements to one only? This would be very awful.

Are there any other options we have got?
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

AW: Constraint violation using OneToOne relationship

Boblitz John
 

> -----Ursprüngliche Nachricht-----
> Von: M. Walter [mailto:[hidden email]]
> Gesendet: Donnerstag, 10. November 2011 09:57
> An: [hidden email]
> Betreff: Re: Constraint violation using OneToOne relationship
>
> This is just unbelievable! As I expected using this property
> has side effects.
>
> We have boolean fields defined in our database as number(1,0)
> because Oracle does not know boolean type. Now we get
> ArgumentExceptions saying that we declare columns that are
> not compatible with the expected type "bit". These errors
> never showed up before we added the property
> "openjpa.jdbc.SchemaFactory".
>
> Now what?
>
> Remove the property and use OneToMany instead of OneToOne
> relationships with programmatically limit the number of child
> elements to one only? This would be very awful.
>
> Are there any other options we have got?
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Constraint-violation-using
> -OneToOne-relationship-tp6978223p6980990.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>

I might be wrong here - but it would seem to me that you are trying
to persist the child, not the parent.  

In your definition "A" has a foreign key to "B" which means "B" must
exist prior to "A" and is thus the parent in the relationship.  This is
also what the error message said "parent key not found".

Have you tried persisting "B" instead of "A"?

Just my 2 cents ...



John

----

Who is General Failure, and why is he reading my hard disk?

Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Constraint violation using OneToOne relationship

kwsutter
Administrator
In reply to this post by mwalter
Hi Walter,
Unfortunately, I think you have hit upon a short-coming of OpenJPA's
foreign key detection mechanism.  OpenJPA has attempted to detect and
process foreign keys according to the definition and usage of the entities,
but it still has a couple of holes.  There are two means of forcing OpenJPA
to do the right thing...

1)  The use of the SchemaFactory property.  But, as you have found out,
there may be some side effects if OpenJPA determines that the metadata for
your database schema doesn't match exactly what's defined in your object
model.

2)  The use of the OpenJPA-specific ForeignKey annotation [1].  Although
this requires a change to your entity code, this explicit declaration will
tell OpenJPA that a foreign key exists for this relationship and
appropriate ordering of the SQL will take place to avoid the constraint
violations you are experiencing.

In your scenario, I would suggest trying the ForeignKey approach and see if
that doesn't get you up and running.

Also, since it sounds like you are a WebSphere customer, you might want to
follow up with a PMR.  Even though it might be thought of as a "feature
request", at least it would be logged for future consideration.

Thanks and Good Luck,
Kevin

[1]
http://openjpa.apache.org/builds/apache-openjpa-1.2.3-SNAPSHOT/docs/manual/manual.html#ref_guide_mapping_jpa_fk


On Thu, Nov 10, 2011 at 2:57 AM, M. Walter <[hidden email]> wrote:

> This is just unbelievable! As I expected using this property has side
> effects.
>
> We have boolean fields defined in our database as number(1,0) because
> Oracle
> does not know boolean type. Now we get ArgumentExceptions saying that we
> declare columns that are not compatible with the expected type "bit". These
> errors never showed up before we added the property
> "openjpa.jdbc.SchemaFactory".
>
> Now what?
>
> Remove the property and use OneToMany instead of OneToOne relationships
> with
> programmatically limit the number of child elements to one only? This would
> be very awful.
>
> Are there any other options we have got?
>
> --
> View this message in context:
> http://openjpa.208410.n2.nabble.com/Constraint-violation-using-OneToOne-relationship-tp6978223p6980990.html
> Sent from the OpenJPA Users mailing list archive at Nabble.com.
>
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Constraint violation using OneToOne relationship

mwalter
Hi Kevin,

thank you VERY much for your answers. I tested your second proposal for solution and it works like a charm! But one drawback here: we lose portability because as you said the annotation is OpenJPA specific.

Could some OpenJPA official please update the FAQ (http://openjpa.apache.org/faq.html) "Can OpenJPA reorder SQL statements to satisfy database foreign key constraints?" Maybe the @ForeignKey solution would be helpful for other desperate developers as well...
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: AW: Constraint violation using OneToOne relationship

mwalter
In reply to this post by Boblitz John
Boblitz John wrote
I might be wrong here - but it would seem to me that you are trying
to persist the child, not the parent.  

In your definition "A" has a foreign key to "B" which means "B" must
exist prior to "A" and is thus the parent in the relationship.  This is
also what the error message said "parent key not found".

Have you tried persisting "B" instead of "A"?
I agree that B must exist prior to A. But in my understanding the parent is referenced by the mappedBy attribute which we have defined in entity B, so A is the parent. I had expected JPA to recognize this relationship and execute the statements in the correct order. But as Kevin explained OpenJPA has tried and failed to detect the relationship unfortunately. I don't think that persisting B instead of A is the right way (well I haven't tried it).

IMHO one advantage to use JPA is that it simplifies the way to work with data and takes the burden to generate the proper SQL statements. Otherwise developers could use plain JDBC and pay attention to all relationships on their own. There would be no reason to use JPA.
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: Constraint violation using OneToOne relationship

mcginty8
This post has NOT been accepted by the mailing list yet.
In reply to this post by mwalter
I got around this sort of issue once on an Oracle db by configuring the constraint to be "Deferrable" (on the db side).
Not likely portable, but it is another way.
Loading...