Hello,

I am having difficulty understanding what I feel is a simple association. I have a "Listing" that is related to a "Category" and belongs to a "Account". I do NOT want there to be a foreign key in either the Category or Account table relating to the Listing. But the Listing table will have foreign keys to both the Account and Category. With those keys I am trying to get hibernate to automatically load the Category and Account for the listing when retrieved. In addition, when I create the listing, I want it to take the Category and Account objects and plug their keys in the foreign key fields in the listing table.

I tried the mapping below, but I got the exception below. Isn't it possible to have the listing table have foreign keys that load the foreign objects ? It appears the error occurs when I am trying to create a NEW listing with an existing Category and Account. I think it is trying to create a new Account and Category when it creates the Listing - but they already exist.

Any help would be greatly appreciated.

Thanks,
Mark


DATABASE PSEUDO SCHEMAS:

Category
OID
NAME
DESC

Account
OID
NAME
DESC

Listing
OID
NAME
DESC
CategoryOID (foreign key to category table)
AccountOID (foreign key to account table)


Hibernate version: 2.1

Mapping documents:
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<!-- DO NOT EDIT: This is a generated file that is synchronized -->
<!-- by MyEclipse Hibernate tool integration. -->
<!-- Created Sat Nov 06 20:54:45 CST 2004 -->
<hibernate-mapping package="testapp.listing">

<class name="ListingImpl" table="listing">
<id name="oid" column="oid" type="testapp.persistence.hibernate.HiberOID">
<generator class="assigned"/>
</id>

<property name="title" column="title" type="java.lang.String" not-null="true" />
<property name="location" column="location" type="testapp.persistence.hibernate.HiberZip" not-null="true" />
<many-to-one name="category" class="testapp.category.CategoryImpl" column="category" outer-join="true"/>
<many-to-one name="seller" class="testapp.account.AccountImpl" column="sellerKey" outer-join="true"/>
</class>

</hibernate-mapping>


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

Full stack trace of any exception that occurs:
net.sf.hibernate.JDBCException: Could not execute JDBC batch update
at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:129)
at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2417)
at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2367)
at net.sf.hibernate.impl.SessionImpl.flush(SessionImpl.java:2236)
at net.sf.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:61)
at testapp.listing.ListingService.process(ListingService.java:311)
at testapp.listing.ListingService.modify(ListingService.java:140)
at dropnow.listing.ListingService.onMessage(ListingService.java:82)
at com.gemstone.message.AsynchronousMessageProcessor$1.run(AsynchronousMessageProcessor.java:98)
Caused by: java.sql.BatchUpdateException: null, message from server: "Cannot add or update a child row: a foreign key constraint fails"
at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1469)
at net.sf.hibernate.impl.BatchingBatcher.doExecuteBatch(BatchingBatcher.java:54)
at net.sf.hibernate.impl.BatcherImpl.executeBatch(BatcherImpl.java:122)
... 8 more

Name and version of the database you are using: mysql 4.1.8 NT

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

In the listing table schema in mysql I had to make the account and category fields within the listing table defined as FOREIGN KEYs.

i had the same problem

I tired giving constraint name?

It didn't work?

SOLUTION: In the listing table schema in mysql I had to make the account and category fields within the listing table defined as FOREIGN KEYs.