Edit Question - KnowledgeHub

Title Body

I have two tables in my MySQL database, which were created like this: CREATE TABLE table1 ( id int auto_increment, name varchar(10), primary key(id) ) engine=innodb and CREATE TABLE table2 ( id_fk int, stuff varchar(30), CONSTRAINT fk_id FOREIGN KEY(id_fk) REFERENCES table1(id) ON DELETE CASCADE ) engine=innodb (These are not the original tables. The point is that table2 has a foreign key referencing the primary key in table 1) Now in my code, I would like to add entries to both of the tables within one transaction. So I set autoCommit to false: Connection c = null; PreparedStatement insertTable1 = null; PreparedStatement insertTable2 = null; try { // dataSource was retreived via JNDI c = dataSource.getConnection(); c.setAutoCommit(false); // will return the created primary key insertTable1 = c.prepareStatement("INSERT INTO table1(name) VALUES(?)",Statement.RETURN_GENERATED_KEYS); insertTable2 = c.prepareStatement("INSERT INTO table2 VALUES(?,?)"); insertTable1.setString(1,"hage"); int hageId = insertTable1.executeUpdate(); insertTable2.setInt(1,hageId); insertTable2.setString(2,"bla bla bla"); insertTable2.executeUpdate(); // commit c.commit(); } catch(SQLException e) { c.rollback(); } finally { // close stuff } When I execute the code above, I get an Exception: MySQLIntegrityConstraintViolationException: Cannot add or update a child row: a foreign key constraint fails It seems like the primary key is not available in the transaction before I commit. Am I missing something here? I really think the generated primary key should be available in the transaction. The program runs on a Glassfish 3.0.1 using mysql-connector 5.1.14 an

Tags (comma-separated) Cancel