We are trying to load a set of data using Hibernate (O/R mapping tool). Hibernate creates foreign key constraints when it is creating our DB (mysql 4.0.14). The process for loading data requires us to break these constraints at certain points, however they will all be satisfied at the end of the transaction.

This is the perfect case for deferred FK constraints, however since mysql does not support deferred FK. I read where we could use

Set foreign_key_checks=0

Do the data load

Set foreign_key_checks=1

However when we tried to run this we still got a FK error message.

Caused by: java.sql.SQLException: General error, message from server: "Cannot delete or update a parent row: a foreign key constraint fails"

at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1628)

at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:886)

at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:945)

at com.mysql.jdbc.Connection.execSQL(Connection.java:1809)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:16 02)

at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:14 88)

at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeUpdate(W rappedPreparedStatement.java:308)

at net.sf.hibernate.persister.NormalizedEntityPersister.delete(NormalizedEn tityPersister.java:606)

at net.sf.hibernate.impl.ScheduledDeletion.execute(ScheduledDeletion.java:2 2)

at net.sf.hibernate.impl.SessionImpl.executeAll(SessionImpl.java:2100)

at net.sf.hibernate.impl.SessionImpl.execute(SessionImpl.java:2066)

... 78 more

Anybody know why the Set foreign_key_checks=0 might not be working?

-John