Re: Foreign key constraint problem.

From: Michael Shea (mik..itido.com)
Date: Thu Jun 18 2009 - 11:59:32 EDT

  • Next message: Michael Gentry: "Re: Cayenne Fetch Limit behaviour ?"

    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
    >



    This archive was generated by hypermail 2.0.0 : Thu Jun 18 2009 - 12:00:21 EDT