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 <a...@maniatis.org> 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 >
