RE: Having two relationships between same two tables

From: Cris Daniluk (cris.danilu..laraview.com)
Date: Tue Apr 05 2005 - 13:33:34 EDT

  • Next message: Dhruti Ramani: "RE: Having two relationships between same two tables"

    Oops. I think you're right... I read the relationship in his example
    backward and thought the quote_id was not the "Accepted quote". Too much
    crack I suppose :)

    > It looks more like insert ordering issue with a circular dependency...
    >
    > * Quote has a "deal_id" so it can't be inserted until the
    > Deal is inserted
    >
    > * Deal has a quote_id for the "accepted quote", so it can't
    > be instered until the quote is inserted.
    >
    > I can think of two alternative ways to solve it:
    >
    > 1. Manually set deferred constraint checking on PostgreSQL -
    > http://www.postgresql.org/docs/8.0/static/sql-set-constraints.html
    >
    > 2. Do a two-step commit. I suspect that in your real
    > application (not just the test example) you won't have an
    > accepted quote until you have all quotes in place, so this
    > error will not occur. Essentially what I am suggesting here
    > is (1) set quote-to-deal relationship, (2) commit, (3) select
    > an "accepted" quote out of all EXISTING quotes and set this
    > relationship, (4) commit.
    >
    > Andrus
    >
    >
    > > Looks like you need to set the "To Dep Pk" property in the Deal
    > > relationship in Quote.
    > >
    > > Or maybe I got that totally backward :)
    > >
    > >
    > > _____
    > >
    > > From: Dhruti Ramani [mailto:dhrutiraman..ahoo.com]
    > > Sent: Tuesday, April 05, 2005 12:54 PM
    > > To: cayenne-use..bjectstyle.org
    > > Subject: Re: Having two relationships between same two tables
    > >
    > >
    > > here is the full error,
    > >
    > > INFO QueryLogger: Created connection pool:
    > > jdbc:postgresql://localhost/scott123
    > > Driver class: org.postgresql.Driver
    > > Min. connections in the pool: 1
    > > Max. connections in the pool: 1
    > > INFO QueryLogger: --- will run 2 queries.
    > > INFO QueryLogger: Opening connection:
    > > jdbc:postgresql://localhost/scott123
    > > Login: postgres
    > > Password: *******
    > > INFO QueryLogger: +++ Connecting: SUCCESS.
    > > INFO QueryLogger: --- transaction started.
    > > INFO QueryLogger: SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE
    > > TABLE_NAME = 'Deal'
    > > INFO QueryLogger: === returned 1 row. - took 62 ms.
    > > INFO QueryLogger: UPDATE AUTO_PK_SUPPORT SET NEXT_ID =
    > NEXT_ID + 20
    > > WHERE TABLE_NAME = 'Deal'
    > > INFO QueryLogger: === updated 1 row.
    > > INFO QueryLogger: +++ transaction committed.
    > > INFO QueryLogger: --- will run 2 queries.
    > > INFO QueryLogger: --- transaction started.
    > > INFO QueryLogger: SELECT NEXT_ID FROM AUTO_PK_SUPPORT WHERE
    > > TABLE_NAME = 'Quote'
    > > INFO QueryLogger: === returned 1 row. - took 2 ms.
    > > INFO QueryLogger: UPDATE AUTO_PK_SUPPORT SET NEXT_ID =
    > NEXT_ID + 20
    > > WHERE TABLE_NAME = 'Quote'
    > > INFO QueryLogger: === updated 1 row.
    > > INFO QueryLogger: +++ transaction committed.
    > > INFO QueryLogger: --- transaction started.
    > > INFO QueryLogger: --- will run 2 queries.
    > > INFO QueryLogger: INSERT INTO Deal (annuityNumber, attorney_id,
    > > comment, dateFunded, dateIn, dead, deal_id, pr_id, quote_id,
    > > sellerBeenDivorced, sellerMarried, unsellable) VALUES (?,
    > ?, ?, ?, ?,
    > > ?, ?, ?, ?, ?, ?,
    > > ?)
    > > INFO QueryLogger: [bind: NULL, NULL, 'Testing123', NULL,
    > NULL, NULL,
    > > 200, NULL, 201, NULL, NULL, NULL] INFO QueryLogger: *** error.
    > > java.sql.SQLException: ERROR: $1 referential integrity violation -
    > > key referenced from deal not found in quote
    > >
    > > at
    > org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
    > > at
    > >
    > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Conn
    > > ection
    > > .java:505)
    > > at
    > >
    > org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1State
    > > ment.j
    > > ava:320)
    > > at
    > >
    > org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2State
    > > ment.j
    > > ava:48)
    > > at
    > >
    > org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc
    > > 1State
    > > ment.java:197)
    > > at
    > >
    > org.objectstyle.cayenne.access.DataNode.runBatchUpdateAsIndividualQuer
    > > ies(Da
    > > taNode.java:593)
    > > at
    > >
    > org.objectstyle.cayenne.access.DataNode.runBatchUpdate(DataNode.java:4
    > > 95)
    > > at
    > >
    > org.objectstyle.cayenne.access.DataNode.performQueries(DataNode.java:3
    > > 53)
    > > at
    > >
    > org.objectstyle.cayenne.access.ContextCommit.commit(ContextCommit.java
    > > :192)
    > > at
    > >
    > org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.j
    > > ava:12
    > > 66)
    > > at
    > >
    > org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.j
    > > ava:12
    > > 36)
    > > at demo.Example.buildExample(Example.java:184)
    > > at demo.Example.main(Example.java:44)
    > > Exception in thread "main"
    > > org.objectstyle.cayenne.CayenneRuntimeException: [v.1.1 December 6
    > > 2004] Commit Exception at
    > >
    > org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.j
    > > ava:12
    > > 75)
    > > at
    > >
    > org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.j
    > > ava:12
    > > 36)
    > > at demo.Example.buildExample(Example.java:184)
    > > at demo.Example.main(Example.java:44)
    > > Caused by: java.sql.SQLException: ERROR: $1 referential integrity
    > > violation - key referenced from deal not found in quote
    > >
    > > at
    > org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
    > > at
    > >
    > org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Conn
    > > ection
    > > .java:505)
    > > at
    > >
    > org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1State
    > > ment.j
    > > ava:320)
    > > at
    > >
    > org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2State
    > > ment.j
    > > ava:48)
    > > at
    > >
    > org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc
    > > 1State
    > > ment.java:197)
    > > at
    > >
    > org.objectstyle.cayenne.access.DataNode.runBatchUpdateAsIndividualQuer
    > > ies(Da
    > > taNode.java:593)
    > > at
    > >
    > org.objectstyle.cayenne.access.DataNode.runBatchUpdate(DataNode.java:4
    > > 95)
    > > at
    > >
    > org.objectstyle.cayenne.access.DataNode.performQueries(DataNode.java:3
    > > 53)
    > > at
    > >
    > org.objectstyle.cayenne.access.ContextCommit.commit(ContextCommit.java
    > > :192)
    > > at
    > >
    > org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.j
    > > ava:12
    > > 66)
    > > ... 3 more
    > >
    > >
    > > Andrus Adamchik <andru..bjectstyle.org> wrote:
    > >
    > > Denna,
    > >
    > > Can you post a SQL log (all queries executed before you got this
    > > exception). This should give a beter idea of what is actually
    > > happenning.
    > >
    > > Andrus
    > >
    > >
    > >> Here is what I did,
    > >>
    > >> From "Deal" I have two relations like this,
    > >> (Name,target)
    > >> (Quotes,Quote) using deal_id. And reverse is (Deal,Deal)
    > >> (AcceptedQuote,Quote) using quote_id.And reverse is
    > >> (DealForAcceptedQuote,Deal)
    > >>
    > >> But then when I try to set particular Quote as "AcceptedQuote" it
    > >> gives me error that: $1 referential integrity violation - key
    > >> referenced from deal not found in quote.
    > >>
    > >> Any ideas?
    > >> Thanks,
    > >> Denna
    > >>
    > >> Cris Daniluk wrote:
    > >> There's absolutely no reason why you can't do this - just
    > create the
    > >> second relationship in the DbEntity, but be sure to select
    > the right
    > >> fields to connect the tables.
    > >>
    > >> On Apr 5, 2005 12:04 PM, Dhruti Ramani wrote:
    > >>> Hello All,
    > >>> I am Denna Ramani. I am getting familiar with Cayenne. I
    > really like
    > >>> this software.
    > >>>
    > >>> We are using postgresql database right now.In one of our
    > project, we
    > >>> need two relations between same two tables. For example, we have
    > >>> "Deal" and "Quote" objects. And relations are, "Deal" can
    > have many
    > >>> "Quotes" but "Deal" has one"AcceptedQuote".
    > "AcceptedQuote" is one
    > >>> of from many Quotes that Deal has. Do you have any idea about how
    > >>> one can do this?
    > >>>
    > >>> Thanks,
    > >>> Denna
    > >>>
    > >>> ________________________________
    > >>> Yahoo! Messenger
    > >>> Show us what our next emoticon should look like. Join the fun.
    > >>>
    > >>>
    > >>
    > >>
    > >>
    > >> ---------------------------------
    > >> Do you Yahoo!?
    > >> Better first dates. More second dates. Yahoo! Personals
    > >
    > >
    > >
    > >
    > >
    > >
    > >
    > > _____
    > >
    > > Yahoo! Messenger
    > > Show us what our next emoticon should look like. Join
    > >
    > <http://us.rd.yahoo.com/evt=31855/*http://advision.webevents.yahoo.com
    > > /emoti
    > > contest> the fun.
    >
    >
    >
    >



    This archive was generated by hypermail 2.0.0 : Tue Apr 05 2005 - 13:33:34 EDT