Ref Integrity problem using many-to-one in composite

nicolasbrasseur · **Joined:** Tue Oct 19, 2004 4:18 am **Posts:** 9

Hibernate version: 2.1.3

Mapping documents:

Code:

<hibernate-mapping>
   <class name="be.loop.bo.Order" table="CUST_ORDER">
      <id name="id">
         <generator class="native"/>
      </id>
      <property name="orderDate" />
      
      <many-to-one name="customer" column="CUSTOMER_ID" />
      
      <list name="orderlines" table="ORDERLINE" lazy="true" cascade="save-update">
         <key column="order_id" />
         <index column="orderline_number"/>
         <composite-element class="be.loop.bo.OrderLine">
           <property name="quantity"/>
           <many-to-one name="product" cascade="save-update">
            <column name="PART_ID" />
            <column name="SUPP_ID" />
           </many-to-one>
         </composite-element>   
      </list>
      
   </class>
</hibernate-mapping>

<hibernate-mapping>
   <class name="be.loop.bo.Product" table="PRODUCT">
      <composite-id>
         <key-property name="partNumber"/>
         <key-property name="supplierNumber"/>
      </composite-id>
      <property name="name"/>
      <property name="description"/>
      <property name="price"/>
      
      <set name="comments" table="COMMENT">
         <key>
            <column name="PART_ID" />
            <column name="SUPP_ID" />
         </key>      
         <composite-element class="be.loop.bo.Comment">
            <parent name="product"/>
            <property name="subject" not-null="true" />
            <property name="comment" not-null="true" />
         </composite-element>         
      </set>
      
      <set name="categories" table="CATEGORY_PRODUCT" inverse="false" cascade="save-update">
         <key>
            <column name="PART_ID" />
            <column name="SUPP_ID" />         
         </key>
         <many-to-many class="be.loop.bo.Category" column="CATEGORY_ID" />
      </set>
      
   </class>

Code between sessionFactory.openSession() and session.close():

Code:

 tx = session.beginTransaction();

            Product p1 = new Product("Cisors", "This is a nice tool", "PTR231","RS", 32.00);              
            OrderLine orderline = new OrderLine(3, p1);
            Order order = new Order(new Date());
            order.addOrderLine(orderline);
            
            session.save(order);
            
            tx.commit();

Full stack trace of any exception that occurs:

Code:

com.sap.dbtech.jdbc.exceptions.BatchUpdateExceptionSapDB: [350]: Referential integrity violated:FK8ECBF022B7C6EF7F,DBA,ORDERLINE(input position 2)
   at com.sap.dbtech.jdbc.CallableStatementSapDB.executeBatch(CallableStatementSapDB.java:605)
   at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
   at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:122)
   at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2410)
   at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2364)
   at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2229)
   at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
   at be.loop.bo.test.MainTest.main(MainTest.java:30)
18:05:26,573  WARN JDBCExceptionReporter:38 - SQL Error: 350, SQLState: 23000
18:05:26,573 ERROR JDBCExceptionReporter:46 - [350]: Referential integrity violated:FK8ECBF022B7C6EF7F,DBA,ORDERLINE(input position 2)
18:05:26,573 DEBUG BatcherImpl:203 - done closing: 0 open PreparedStatements, 0 open ResultSets
18:05:26,573 DEBUG BatcherImpl:261 - closing statement

Name and version of the database you are using: MAXDB 7.5.1

The generated SQL (show_sql=true):

Code:

CREATE TABLE "DBA"."ORDERLINE"
(
   "ORDER_ID"               Fixed (19,0)    NOT NULL,
   "QUANTITY"               Integer,
   "PART_ID"               Varchar (255) ASCII,
   "SUPP_ID"               Varchar (255) ASCII,
   "ORDERLINE_NUMBER"               Integer    NOT NULL,
   PRIMARY KEY ("ORDER_ID", "ORDERLINE_NUMBER"),
   FOREIGN KEY "FK8ECBF0224991FFAC"   ("ORDER_ID") REFERENCES "DBA"."CUST_ORDER" ("ID") ON DELETE  RESTRICT,
   FOREIGN KEY "FK8ECBF022B7C6EF7F"   ("PART_ID","SUPP_ID") REFERENCES "DBA"."PRODUCT" ("PARTNUMBER","SUPPLIERNUMBER") ON DELETE  RESTRICT
)

CREATE TABLE "DBA"."CUST_ORDER"
(
   "ID"               Fixed (19,0)    NOT NULL,
   "ORDERDATE"               Timestamp,
   "CUSTOMER_ID"               Fixed (19,0),
   PRIMARY KEY ("ID"),
   FOREIGN KEY "FKDA1A664216B4891C"   ("CUSTOMER_ID") REFERENCES "DBA"."CUSTOMER" ("ID") ON DELETE  RESTRICT
)

CREATE TABLE "DBA"."PRODUCT"
(
   "PARTNUMBER"               Varchar (255) ASCII    NOT NULL,
   "SUPPLIERNUMBER"               Varchar (255) ASCII    NOT NULL,
   "NAME"               Varchar (255) ASCII,
   "DESCRIPTION"               Varchar (255) ASCII,
   "PRICE"               Float (20),
   PRIMARY KEY ("PARTNUMBER", "SUPPLIERNUMBER")
)

My Problem:

I am pretty new to Hibernate, I try to build an example using a collection of components (OrderLine instances) to build a relationship between Orders and Products.
I get an exception when trying to save a simple graph of objects telling me I do not respect the referential integrity of the DB ... It seems that there is a problem with FOREIGN KEY "FK8ECBF022B7C6EF7F" referencing my products.
Constraints :
- I want to keep a natural key spread on 2 columns in the product table
- I want to keep a List in the Order class.

Any idea of what i did wrong ?

thanks for helping ...

Nicolas[/code]

nicolasbrasseur · **Joined:** Tue Oct 19, 2004 4:18 am **Posts:** 9

Code:

            tx = session.beginTransaction();

            Product p1 = new Product("Cisors", "This is a nice tool", "PTR231","RS", 32.00);              
            session.save(p1);
           
            OrderLine orderline = new OrderLine(3, p1);
            Order order = new Order(new Date());
            order.addOrderLine(orderline);
            
            session.save(order);
            
            tx.commit();

It works well when I first save the product ... How is possible to tell hibernate first to save (execute the insert statement) the new (transient) Product before the inserting the orderline ? I thought cascad="save-update" will help ...

Any idea ?

Nicolas

anthony · **Posted:** Sat Oct 23, 2004 2:39 pm

add session.flush() just after
session.save(p1);

nicolasbrasseur · **Joined:** Tue Oct 19, 2004 4:18 am **Posts:** 9

Hello Anthony,

thanks for your answer but I don't want to explicitely save p1. I'd like to save it when saving the Order (see code in my first post) ... unfortunately Hibernate first try to insert the Orderline before inserting the product resulting in a referential integrity Exception.

Any idea how to make Hibernate save the Product before the OrderLine ? I set the cascade to save-update ... doesn't change anything ...

Thanks for your help

Nicolas

nicolasbrasseur · **Joined:** Tue Oct 19, 2004 4:18 am **Posts:** 9

Does anybody have an idea of what I did wrong ?

Nicolas