I resolved my problem. It was to do with the order I was doing things in: start transaction rawCayenneCmd( "SET FOREIGN_KEY_CHECKS=0"); dataStore.getEM().deletePersistent( metric); dataStore.getEM().deletePersistent( thresholdChange); rawCayenneCmd( "SET FOREIGN_KEY_CHECKS=1"); commit transaction
I changed the order of the last two lines and it works now. ~ Chris Murphy Seaweed Software P/L (www.strandz.org) On 24 September 2011 17:18, Aristedes Maniatis <[email protected]> wrote: > On 24/09/11 1:26 PM, Dirk Olmes wrote: > >> On 09/22/2011 06:21 PM, Chris Murphy (www.strandz.org) wrote: >> >>> I want to delete a row from a particular table. Unfortunately the table >>> in >>> question has a FK constraint to another table, which in turn has a FK >>> constraint back to the table in question. Even the latest version of >>> MySql >>> (I tried 5.5) does constraint checking on a per-statement rather than >>> per-transaction basis. Basically the constraints are always tripped and >>> the >>> only way I've found to allow the delete to proceed is to drop the FK >>> constraints. >>> >> >> I'm not a MySQL expert but the usual approach I used with FK constraint >> is to declare them as DEFERRABLE INITIALLY DEFERRED. This way, the >> constraints won't be checked until all the updates to the tables have >> been made. >> > > I don't believe the InnoDB table type in mySQL supports deferred > constraints. I wish it did. > > The original poster might be caught out by the fact that disabling > constraints is only effective within the current connection session. So a > second connection may be a separate session as far as mysql is concerned. > > Ari > > > -- > --------------------------> > Aristedes Maniatis > GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A >
