Agreed. We've started following that practice too. Always assign a simple unrelated primary key. Consistent, avoids surprises.
On Thu, Feb 17, 2011 at 9:09 AM, Mike Kienenberger <[email protected]>wrote: > Solution C: assign a meaningless primary key to the join table. > > Obviously, this isn't an option for everyone. > > On Thu, Feb 17, 2011 at 3:11 AM, Aristedes Maniatis <[email protected]> > wrote: > > On 15/02/11 2:04 PM, Marcin Skladaniec wrote: > >> > >> java.sql.SQLIntegrityConstraintViolationException: The statement was > >> aborted because it would have caused a duplicate key value in a unique > or > >> primary key constraint or unique index identified by > 'TAGRELATION_UNIQUE' > >> defined on 'TAGRELATION'. > > > > > > Can I summarise, since I think you've highlighted a problem which is > > completely different to your email subject. > > > > 1. Table A has primary key which is user defined (in this case, it is the > > join between two other tables, so the PK is the compound of the two > foreign > > keys) > > > > 2. Create record in table A. Commit. > > > > 3. Delete that record in table A. > > > > 4. Create another record in table A with the same PK as the one just > deleted > > (since it joins the same other two tables). > > > > 5. Commit -> ERRROR > > > > > > The create in step 4 is being committed to the db before the delete in > step > > 3, failing the key constraint. > > > > > > Use case > > -------- > > You may wonder why on earth this is happening. Well, in a rich client > > application a user might tick a checkbox to link two records and then > untick > > it and tick it again while they make up their mind. All those changes > result > > in changes to the context. > > > > > > Solution > > -------- > > > > A. Dedupe the overlapping create/delete records in the context before > > committing. > > B. Order the SQL better. > > > > > > > > Any thoughts about how Cayenne could deal with this better? What do other > > ORMs do in this scenario? > > > > > > > > Ari > > > > > > -- > > --------------------------> > > Aristedes Maniatis > > GPG fingerprint CBFB 84B4 738D 4E87 5E5C 5EFA EF6A 7D2E 3E49 102A > > >
