Many-to-many, sequence and constraint violation

mopt · **Posted:** Mon Nov 17, 2003 6:01 am

Hello,

I have 2 tables with a join table and a many-to-many relationship :
A --> AxB --> B

A objects have a set of B objects. B objects do not have knowledge of A, but B objects can belong to many A objects, this is why I need a many to many relationship

The A-id is generated with an oracle sequence.

When I try to save a A object with a set of B objects (which are already in DB and don't need to be saved) hibernate save A but when it try to fill the join table I have a "violation constraint" : hibernate doesn't find the primary key of A to fill the primary key of the element in the join table.

Have someone a idea to solve this problem ?
Thanks in advance.

gavin · **Posted:** Mon Nov 17, 2003 6:20 am

Sorry, nowhere near enough information.

mopt · **Posted:** Mon Nov 17, 2003 7:01 am

A :

Code:

<hibernate-mapping>
   <class name="Parent" table="PARENTS">
      <id name="numParent" column="PK_PARENT" unsaved-value="any">
         <generator class="sequence">
             <param name="sequence">S_PARENT</param>
         </generator>
      </id>
[blabla]
      <set name="Children" table="PARENTS_CHILDREN" inverse="false" lazy="true">
          <key column="FK_PARENT"/>
          <many-to-many class="Child" column="FK_CHILD"/>
      </set>
    </class>
</hibernate-mapping>

B :

Code:

<hibernate-mapping>
   <class name="Child" table="CHILDREN">
      <id name="numChild" column="PK_CHILD">
         <generator class="assigned"/>
      </id>
[blabla : no reference to "parent"]
   </class>
</hibernate-mapping>

My java code :

Code:

Set children = new HashSet () ;
String queryString = "select baby from Child as baby" ;

beginTransaction () ;
Query query = session.createQuery (queryString) ;
Iterator it = query.iterate () ;
         while (it.hasNext ())
         {
            Child baby = (Child) it.next () ;
            children.add (baby) ;
          }

parent.setChildren (children) ;
session.save (parent) ;
endTransaction (true) ;

My exception :

[code]
Hibernate: insert into PARENTS ([blabla], PK_PARENTS) values ([blabla],?)

Hibernate: insert into PARENTS_CHILDREN (FK_PARENT, FK_CHILD) values (?, ?)

17 nov. 2003 11:56:12 net.sf.hibernate.util.JDBCExceptionReporter logExceptions

ATTENTION: SQL Error: 2291, SQLState: 23000

17 nov. 2003 11:56:12 net.sf.hibernate.util.JDBCExceptionReporter logExceptions

GRAVE: ORA-02291: violation de contrainte (USER.PARENTS_CHILDREN_1) d'int

gavin · **Posted:** Mon Nov 17, 2003 7:30 am

Well, it all looks ok to me. The SQL Hibernate executes looks correct also. It first inserts the parent, then the link. All correct.

You'd better enable Hibernate's parameter logging and see exactly what values are being bound to the JDBC parameters.

What is the English translation of the Oracle error message?

mopt · **Posted:** Mon Nov 17, 2003 7:40 am

The translation is :

Code:

ERROR: ORA-02291: integrity constraint (USER.PARENTS_CHILDREN_1) violated - parent key not found

My constraint is :

Code:

ALTER TABLE PARENTS_CHILDREN ADD ( CONSTRAINT PARENTS_CHILDREN_1 FOREIGN KEY (FK_PARENT) REFERENCES PARENTS (PK_PARENT));

mopt · **Posted:** Mon Nov 17, 2003 9:41 am

As required, I used the logging.
Here is my debug for the insertion of my "parent" :

Code:

14:22:36,093 DEBUG BatcherImpl:228 - prepared statement get: insert into PARENTS ([blabla], PK_PARENT) values [blabla], ?)
Hibernate: insert into PARENTS ([blabla], PK_PARENT) values ([blabla], ?)
14:22:36,093 DEBUG BatcherImpl:234 - preparing statement
14:22:36,093 DEBUG EntityPersister:398 - Dehydrating entity: [Parent#47]
[blabla]
14:22:36,109 DEBUG LongType:46 - binding '47' to parameter: 12

The 12th position is my PK_PARENT = 47

Now the debug for the join table :

[code]
14:22:36,171 DEBUG BatcherImpl:228 - prepared statement get: insert into PARENTS_CHILDREN (FK_PARENT, FK_CHILD) values (?, ?)
Hibernate: insert into PARENTS_CHILDREN (FK_PARENT, FK_CHILD) values (?, ?)
14:22:36,171 DEBUG BatcherImpl:234 - preparing statement
14:22:36,187 DEBUG LongType:46 - binding '47' to parameter: 1
14:22:36,187 DEBUG IntegerType:46 - binding '1' to parameter: 2
14:22:36,187 DEBUG BatcherImpl:26 - Adding to batch
14:22:36,187 DEBUG LongType:46 - binding '47' to parameter: 1
14:22:36,187 DEBUG IntegerType:46 - binding '0' to parameter: 2
14:22:36,187 DEBUG BatcherImpl:26 - Adding to batch
14:22:36,187 DEBUG BasicCollectionPersister:570 - done inserting collection
14:22:36,187 DEBUG BatcherImpl:48 - Executing batch size: 2
14:22:36,218 DEBUG JDBCExceptionReporter:36 - SQL Exception
java.sql.BatchUpdateException: ORA-02291: violation de contrainte (USER.PARENTS_CHILDREN_1) d'int

gavin · **Posted:** Mon Nov 17, 2003 9:57 am

Looks all perfect to me. Hibernate is doing the right thing.

Perhaps you have some other constraints floating around in the db.

mopt · **Posted:** Mon Nov 17, 2003 10:24 am

Unfortunately I can't see anything else.

Anyway, thanks a lot...

alvim · **Posted:** Tue May 04, 2004 1:30 pm

Hi!

I'm with the same problem but only with Oracle 9 thin driver. When I use the same application (and hibernate) with Oracle 8 thin driver everything is ok!

Any news here?

Thanks in advance...

Paulo