Thanks Andrus. And yes, all columns in ProductRelation are PK's and FK's. The log message was a successful one as I need to show the DELETE statement appears after the INSERT statement.
The below is an example with error. I tried to add a new ProductRelation record to a product which already has one in the ProductRelation table. dao.ProductDaoImpl.saveProductRelation(ProductDaoImpl.java:714) - <product.getProductRelationArray().size()=1> dao.ProductDaoImpl.saveProductRelation(ProductDaoImpl.java:721) - <relatedProductId[0]=1> dao.ProductDaoImpl.saveProductRelation(ProductDaoImpl.java:721) - <relatedProductId[1]=220> cayenne.access.QueryLogger.logQueryStart(QueryLogger.java:459) - <--- will run 2 queries.> cayenne.access.QueryLogger.logBeginTransaction(QueryLogger.java:413) - <--- transaction started.> cayenne.access.QueryLogger.logQuery(QueryLogger.java:336) - <INSERT INTO dbo.ProductRelation (ProductId, RelatedProductid, TypeId) VALUES (?, ?, ?)> cayenne.access.QueryLogger.logQueryParameters(QueryLogger.java:358) - <[bind: 1, 220, 16]> cayenne.access.QueryLogger.logUpdateCount(QueryLogger.java:404) - <=== updated 1 row.> cayenne.access.QueryLogger.logQueryParameters(QueryLogger.java:358) - <[bind: 1, 1, 16]> cayenne.access.QueryLogger.logQueryError(QueryLogger.java:439) - <*** error.> on: Violation of PRIMARY KEY constraint 'PK_ProductRelation'. Cannot insert duplicate key in object 'dbo.ProductRelation'. -----Original Message----- From: Andrus Adamchik [mailto:[EMAIL PROTECTED] Sent: Friday, 28 April 2006 11:05 PM To: [email protected] Subject: Re: How to enforce Delete before Insert? Ho is the PK defined in ProductRelation? Looks like all three columns are a part of PK. If so why would the error be thrown - the rows you are deleting and inserting are not the same. I wonder if that's a SQLServer thing? Could you post the error. Andrus On Apr 27, 2006, at 9:00 AM, Bill Fan wrote: > Hi, > > I have a Product & a ProductRelation tables. When the ProductRelation > records for a product are managed in a web page, I'd like to delete > the > exiting records in the ProductRelation table for this product > first, then > insert the new one. > > I'm having trouble to do this with the following code. I got the > "Cannot > insert duplicate key in object ..." db error. The reason appears > that the > DELET statements appear after the INSERT statements from what I can > see in > the QueryLogger output. Could someone please help? I'm using > Cayenne 1.2B2. > > > The code: > ========= > public void saveProductRelation(Product product, String > relatedProductId[], Type relationType) > { > // delete the existing records > log.debug("product.getProductRelationArray().size()=" + > product.getProductRelationArray().size()); > > threadDataContext().deleteObjects(product.getProductRelationArray()); > > if (relatedProductId.length > 0) > { > for (int i=0; i<relatedProductId.length; i++) > { > log.debug("relatedProductId[" + i + "]=" + > relatedProductId[i]); > > ProductRelation o = new ProductRelation(); > threadDataContext().registerNewObject(o); > o.setToProduct(product); > o.setToProduct1((Product) objectForPK(Product.class, new > Integer(relatedProductId[i].trim()))); > o.setToType(relationType); > > product.addToProductRelationArray(o); > } > } > > commitChanges(); > } > > > The QueryLogger output: > ======================= > dao.ProductDaoImpl.saveProductRelation(ProductDaoImpl.java:713) - > <relatedProductId[0]=9> > cayenne.access.QueryLogger.logQueryStart(QueryLogger.java:459) - > <--- will > run 2 queries.> > cayenne.access.QueryLogger.logBeginTransaction(QueryLogger.java: > 413) - <--- > transaction started.> > cayenne.access.QueryLogger.logQuery(QueryLogger.java:336) - <INSERT > INTO > dbo.ProductRelation (ProductId, RelatedProductid, TypeId) VALUES > (?, ?, ?)> > cayenne.access.QueryLogger.logQueryParameters(QueryLogger.java:358) - > <[bind: 242, 9, 16]> > cayenne.access.QueryLogger.logUpdateCount(QueryLogger.java:404) - <=== > updated 1 row.> > cayenne.access.QueryLogger.logQuery(QueryLogger.java:336) - <DELETE > FROM > dbo.ProductRelation WHERE ProductId = ? AND RelatedProductid = ? > AND TypeId > = ?> > cayenne.access.QueryLogger.logQueryParameters(QueryLogger.java:358) - > <[bind: 242, 222, 16]> > > > Many thanks in advace! > > Bill > > >
