This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
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 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?