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
