cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Shea <m...@nitido.com>
Subject Re: Foreign key constraint problem.
Date Thu, 18 Jun 2009 15:59:32 GMT
I've actually already tried using a non-meaningful primary key, but I 
still want to have a unique constraint on the columns in question if I 
do that, because it should not be possible to have an Email assigned to 
the same Task twice.... Doing it with a unique constraint results in the 
same problem as using these columns as the primary key =).

I agree that one should just alter the record rather than deleting and 
re-adding it... Unfortunately, I am providing a library, and given the 
API that we've got, I can't really prohibit front-end developers from 
doing this; I can only try to deal with the fallout if they do.

I looked into InnoDB a bit, which is the storage system I am using. It 
looks like InnoDB doesn't defer checking of constraints to the end of 
the transaction. From this link 
(http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html):

"*Deviation from SQL standards*: Like MySQL in general, in an SQL 
statement that inserts, deletes, or updates many rows, |InnoDB| checks 
|UNIQUE| and |FOREIGN KEY| constraints row-by-row. According to the SQL 
standard, the default behavior should be deferred checking. That is, 
constraints are only checked after the /entire SQL statement/ has been 
processed. Until |InnoDB| implements deferred constraint checking, some 
things will be impossible, such as deleting a record that refers to 
itself via a foreign key."

Anyway. It sounds like it would be *possible* to get around this by 
using SET FOREIGN_KEY_CHECKS=0 and SET_FOREIGN_KEY_CHECKS=1 at the 
start/end of transactions... But I believe that this would also mean 
that any keys modified during the transactions wouldn't have integrity 
checks enforced at all (Source: 
http://code.djangoproject.com/ticket/3615), so although this could solve 
my particular problem, it could definitely introduce other ones =).

This all implies to me that the real bug here is with InnoDB, and not 
with Cayenne. Thanks for the help, I'll just work around it until such 
time as InnoDB gets fixed ;).


Shea.

> On 18/6/09 7:51 AM, Michael Shea wrote:
>> It looks to me like Cayenne is attempting to insert the new row before
>> deleting the old one; I notice that in DataNode.performQueries, the
>> collection of queries contains 3 queries: An InsertBatchQuery, an
>> UpdateBatchQuery and a DeleteBatchQuery. Looks like the insert and
>> update are being run before the delete to me, although I haven't looked
>> too deeply into this code to try to figure out what's going on.
>
> You can turn debugging on and see the SQL statements being generated 
> by Cayenne to verify what is happening. But my guess is that since the 
> entire operation is in one transaction, MySQL is performing constraint 
> checks before committing that transaction.
>
> Perhaps you should consider using a non-meaningful generated primary 
> key, or just altering the record rather than deleting and recreating it.
>
> Ari Maniatis
>


Mime
View raw message