aspose file tools*
The moose likes Object Relational Mapping and the fly likes Hibernate MySQL Auto increment insert inc. field been a foreign key Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Hibernate MySQL Auto increment insert inc. field been a foreign key" Watch "Hibernate MySQL Auto increment insert inc. field been a foreign key" New topic
Author

Hibernate MySQL Auto increment insert inc. field been a foreign key

Ally Cavs
Ranch Hand

Joined: Aug 25, 2008
Posts: 89
Hi

The subject is confusing but let me explain
I have a MySQL DB

I have a table called BOOK whose primary key(bookID) is a auto increment field
My Book object has an attribute id which I set to 0 as mySQL knows that when doing an insert, the auto increment field will be set to zero. MySQL implicitly sets the id to the correct auto incremented value for that insert.
That all works fine

But now I have a Chapters table
whose has the following fields

bookID
chapterID
chapterDescription

bookID and chapterID make up a composite Key

Now if under all the one transaction I want to insert a record into the BOOK table and the chapter table where by the chapter table is inserting records for the record just inserted into BOOK I get a
com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails

I believe this down to be the fact that my book object has an ID of zero. The insert happens into book table.
The commit hasnt happened yet but mySQL has converted zero to the next value on the book table which is fine

The chapter object has a book id attribute of 0 and hence 0 does not exist on the Book table. I debugged and my Book object hasnt yet changed the ID attribute. There has been no commit so, thats what I would expect.

This has to be a common problem for autoincrement fields in MySQL
Any advice on how to get around such an issue?

Thanks
Ally

Andreas Erber
Greenhorn

Joined: Oct 03, 2009
Posts: 20
Hi Ally Cavs,

you shouldn't worry too much about IDs. Usually these are surrogate keys with no meaning to the application. Except when loading an object by ID you hardly get in touch with it.

Let your Chapter class reference a Book instance but not the book ID. Hibernate will take care of the rest.

CU
Froestel
Ally Cavs
Ranch Hand

Joined: Aug 25, 2008
Posts: 89
Wow thats interesting. I was going to hack some code using MySQL LAST_INSERT_ID() function

can you give sample code(I presume XML) how I could get a chapter instance mapped to a book instance please?
Ally Cavs
Ranch Hand

Joined: Aug 25, 2008
Posts: 89
Do you mean I have a book attribute in my chapter class.

or are you talking about XML
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Hibernate MySQL Auto increment insert inc. field been a foreign key