Problems with FK contraints in Object creation.

From: Jorge Sopena (jsopen..idsa.es)
Date: Tue Nov 30 2004 - 13:07:14 EST

  • Next message: Andrus Adamchik: "Re: Problems with FK contraints in Object creation."

    Hi,
    I'm having problems when I create two objects and commit them. When
    there is a FK between them, I get a
    "java.sql.SQLException: General error, message from server: "Cannot add
    or update a child row: a foreign key constraint fails"

    The real problem is more complex but I test this simple case and the
    problem arises too.
    I have two objects, SmsUser and Profile. SmsUser has a FK to Profile.
    When I create both objects and try to commit them, I've got the exception.
    It's because Cayenne is trying to save the SmsUser first, then the Profile.
    The log generated is:

        INFO QueryLogger: LOCK TABLES AUTO_PK_SUPPORT WRITE
        INFO QueryLogger: SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE
        TABLE_NAME = 'profile'
        INFO QueryLogger: UPDATE AUTO_PK_SUPPORT SET NEXT_ID = NEXT_ID + 20
        WHERE TABLE_NAME = 'profile' AND NEXT_ID = 260
        INFO QueryLogger: UNLOCK TABLES
        INFO QueryLogger: LOCK TABLES AUTO_PK_SUPPORT WRITE
        INFO QueryLogger: SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE
        TABLE_NAME = 'smsuser'
        INFO QueryLogger: UPDATE AUTO_PK_SUPPORT SET NEXT_ID = NEXT_ID + 20
        WHERE TABLE_NAME = 'smsuser' AND NEXT_ID = 320
        INFO QueryLogger: UNLOCK TABLES
        INFO QueryLogger: --- transaction started.
        INFO QueryLogger: --- will run 2 queries.
        INFO QueryLogger: INSERT INTO smsuser (Name, Password,
        Profile_idProfile, Username, idUser) VALUES (?, ?, ?, ?, ?)
        INFO QueryLogger: [bind: 'kiko', 'º¨Äçg4×ÅEX¼-_', 260, 'kiko', 320]
        INFO QueryLogger: *** error.
        java.sql.SQLException: General error, message from server: "Cannot
        add or update a child row: a foreign key constraint fails"

    I've seen there is a Issue(CAY-87
    <http://objectstyle.org/jira/secure/ViewIssue.jspa?key=CAY-87>) open
    about this problem but when there is a relationship cycle. This case I
    think is differente and a very common situation.
    I'm working with Cayenne-1.1RC3.
    I also attach my Database Map, if it's useful to find the problem.

    Any hint?

    Thanks!!!

    Jorge


    This archive was generated by hypermail 2.0.0 : Tue Nov 30 2004 - 13:03:09 EST