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
A foreign key (FK) is one or more columns in one table that are used as a primary key in another table. First, we’ll look at these concepts in a simple example, and then we’ll develop a JDBC solution and a test client program to show these relationships using DatabaseMetaData.getImportedKeys ().
A foreign key constraint doesn't have to be linked only to a primary key constraint in another table. Foreign keys can also be defined to reference the columns of a UNIQUE constraint in another table.
FOREIGN KEY constraints are not enforced on temporary tables. If a foreign key is defined on a CLR user-defined type column, the implementation of the type must support binary ordering. For more information, see CLR User-Defined Types. A column of type varchar(max) can participate in a FOREIGN KEY...
In the Foreign-key Relationships dialog box, select Add. The relationship appears in the Selected Relationship list with a system-provided name in the format FK_< tablename >_< tablename >, where tablename is the name of the foreign key table.
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);
}
...
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With