Foreign Key Constraints

[John Towell]

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