cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Chris Murphy (www.strandz.org)" <ch...@strandz.org>
Subject Recursive FK constraints in MySql
Date Thu, 22 Sep 2011 16:21:57 GMT
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 followed this thread (
http://cayenne.195.n3.nabble.com/Foreign-key-constraint-problem-td7536.html)
which talks about the problem and suggests that something like this might
work:

start transaction
rawCayenneCmd( "SET FOREIGN_KEY_CHECKS=0");
dataStore.getEM().deletePersistent( metric);
rawCayenneCmd( "SET FOREIGN_KEY_CHECKS=1");
commit transaction

    private void rawCayenneCmd( String cmdStr)
    {
        SQLTemplate rawCmd = new SQLTemplate( ThresholdChange.class,
cmdStr);
        DataContext context = (DataContext)dataStore.getEM().getActualEM();
        context.performQuery( rawCmd);
    }

It doesn't work for me. Have I done something wrong in the implementation
here?

 ~ Chris Murphy

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message