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(AbstractJdbc1Connection
> .java:505)
> at
> org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.j
> ava:320)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
> ava:48)
> at
> org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1State
> ment.java:197)
> at
> org.objectstyle.cayenne.access.DataNode.runBatchUpdateAsIndividualQueries(Da
> taNode.java:593)
> at
> org.objectstyle.cayenne.access.DataNode.runBatchUpdate(DataNode.java:495)
> at
> org.objectstyle.cayenne.access.DataNode.performQueries(DataNode.java:353)
> at
> org.objectstyle.cayenne.access.ContextCommit.commit(ContextCommit.java:192)
> at
> org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.java:12
> 66)
> at
> org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.java: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.java:12
> 75)
> at
> org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.java: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(AbstractJdbc1Connection
> .java:505)
> at
> org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.j
> ava:320)
> at
> org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
> ava:48)
> at
> org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1State
> ment.java:197)
> at
> org.objectstyle.cayenne.access.DataNode.runBatchUpdateAsIndividualQueries(Da
> taNode.java:593)
> at
> org.objectstyle.cayenne.access.DataNode.runBatchUpdate(DataNode.java:495)
> at
> org.objectstyle.cayenne.access.DataNode.performQueries(DataNode.java:353)
> at
> org.objectstyle.cayenne.access.ContextCommit.commit(ContextCommit.java:192)
> at
> org.objectstyle.cayenne.access.DataContext.commitChanges(DataContext.java: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:28:08 EDT