You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by "Chris Murphy (www.strandz.org)" <ch...@strandz.org> on 2011/09/22 18:21:57 UTC
Recursive FK constraints in MySql
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
Re: Recursive FK constraints in MySql
Posted by "Chris Murphy (www.strandz.org)" <ch...@strandz.org>.
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 <ar...@maniatis.org> 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
>
Re: Recursive FK constraints in MySql
Posted by Aristedes Maniatis <ar...@maniatis.org>.
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
Re: Recursive FK constraints in MySql
Posted by Dirk Olmes <di...@xanthippe.ping.de>.
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.
-dirk
RE: Recursive FK constraints in MySql
Posted by "Durchholz, Joachim" <Jo...@hennig-fahrzeugteile.de>.
> 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.
You could set a field to NULL first, breaking the cycle in the data.
That way, you get a record that you can delete, and unravel the FK chain from there.
As said in the thread you linked, Cayenne might sabotage you by reordering actions, so you might want to work around that by dropping down to raw SQL or by committing after each step.
I don't know why disabling FK checking doesn't work for you.
But then we don't know what errors you got from that attempt, so it's a bit hard to guess what might be wrong with your code.
Regards,
Jo