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
