[CAY-536] "duplicate key" error on commit

• Type: Bug
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 1.2 branch
• Component/s: Core Library
• Environment: XPP, MSSQL 2005 Express (MSSQL 2000 should be fine I guess), Tomcat 5.0.28, Spring 1.2.7
• Assignee: Andrus Adamchik
• Reporter: Bill Fan
• Created: 29/Apr/06 05:47
• Updated: 30/Jun/06 23:31

Description

We have Product & ProductRelation tables. When we need to add a new ProductRelation record to a product, we like to delete all the existing ProductRelation records first, then add the new one.

We are having the "Cannot insert duplicate key..." error. It appears that the DELETE statement is executed after the INSERT statement.

Here is the stripped down datamap:

<db-entity name="Product" schema="dbo" catalog="iCon">
<db-attribute name="ProductId" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="10"/>
<db-attribute name="ProductName" type="VARCHAR" isMandatory="true" length="100"/>
</db-entity>
<db-entity name="ProductRelation" schema="dbo" catalog="iCon">
<db-attribute name="ProductId" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="10"/>
<db-attribute name="RelatedProductid" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="10"/>
<db-attribute name="TypeId" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="10"/>
</db-entity>
<db-entity name="Type" schema="dbo" catalog="iCon">
<db-attribute name="Description" type="VARCHAR" isMandatory="true" length="50"/>
<db-attribute name="TypeId" type="INTEGER" isPrimaryKey="true" isMandatory="true" length="10"/>
</db-entity>

<obj-entity name="Product" className="fanscom.model.Product" lock-type="optimistic" dbEntityName="Product" superClassName="org.objectstyle.cayenne.CayenneDataObject">
<obj-attribute name="productName" type="java.lang.String" db-attribute-path="ProductName"/>
</obj-entity>
<obj-entity name="ProductRelation" className="fanscom.model.ProductRelation" lock-type="optimistic" dbEntityName="ProductRelation" superClassName="org.objectstyle.cayenne.CayenneDataObject">
</obj-entity>
<obj-entity name="Type" className="fanscom.model.Type" lock-type="optimistic" dbEntityName="Type" superClassName="org.objectstyle.cayenne.CayenneDataObject">
<obj-attribute name="description" type="java.lang.String" db-attribute-path="Description"/>
</obj-entity>

<db-relationship name="productRelationArray" source="Product" target="ProductRelation" toDependentPK="true" toMany="true">
<db-attribute-pair source="ProductId" target="ProductId"/>
</db-relationship>
<db-relationship name="productRelationArray1" source="Product" target="ProductRelation" toDependentPK="true" toMany="true">
<db-attribute-pair source="ProductId" target="RelatedProductid"/>
</db-relationship>
<db-relationship name="toProduct" source="ProductRelation" target="Product" toMany="false">
<db-attribute-pair source="ProductId" target="ProductId"/>
</db-relationship>
<db-relationship name="toProduct1" source="ProductRelation" target="Product" toMany="false">
<db-attribute-pair source="RelatedProductid" target="ProductId"/>
</db-relationship>
<db-relationship name="toType" source="ProductRelation" target="Type" toMany="false">
<db-attribute-pair source="TypeId" target="TypeId"/>
</db-relationship>