JDBC: foreign key on PK created in same transaction

Question

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 and MySQL 5.5.8

Any help is really appreciated!

Regards, hage

score 5 · Accepted Answer · answered May 19 '11 at 10:15

5

You missed something for the returned updated id , you have to do like this :

Long hageId = null;

try {
    result = insertTable1.executeUpdate();
} catch (Throwable e) {
    ...
}

ResultSet rs = null;

try {
    rs = insertTable1.getGeneratedKeys();
    if (rs.next()) {
        hageId = rs.getLong(1);
    }
 ...

answered May 19 '11 at 10:15

EricParis16

809
5
9

Thank you! It works perfectly now. But something is still unclear to me: My tables were empty before I run the code. So `exececuteUpdate` returned the number of affected rows (in this case 1) which is also the generated ID. I think this should have worked too - also it would have raised the exception in a second run... Or does `getGeneratedKeys()` make the keys available? – hage May 19 '11 at 12:36
The inserted row id start to last row + 1, unless you do a 'truncate' on your table, it will not initialized to 0. – EricParis16 May 21 '11 at 17:50
Is it supposed to be `while(rs.next())` – Identity1 Jan 11 '16 at 11:02

Shilaghae · Answer 2 · 2011-05-19T10:20:43.880

1

Instead of using executeUpdate() use execute() and then return the primary key.

http://www.coderanch.com/t/301594/JDBC/java/Difference-between-execute-executeQuery-executeUpdate

But I don't work with db...so I could do a mistake

edited May 19 '11 at 10:20

answered May 19 '11 at 10:15

Shilaghae

957
12
22

I know the differences between the different execute*() methods. I was just trying to get the key the wrong way. I thought executeUpdate() would return the key rather than the number of changed rows when `Statement.RETURN_GENERATED_KEYS` is set. But thanks for your answer anyway. – hage May 19 '11 at 13:58

JDBC: foreign key on PK created in same transaction

2 Answers2

Linked