Deleting products from ac_Products when also in wishlist

[AbleMods]

When I'm doing a delete statement in SQL on the ac_Products, it's throwing a SQL error if the product is also on a wishlist. Any suggestions for a workaround?

Code: Select all

delete from ac_products 
where visibilityid = 2

throws

The DELETE statement conflicted with the REFERENCE constraint "ac_Products_ac_WishlistItems_FK1". The conflict occurred in database "Solunar", table "dbo.ac_WishlistItems", column 'ProductId'.

[ZLA]

You could delete the same products from ac_WishlistItems first using:

DELETE FROM ac_WishlistItems WHERE productid in
(select productid from ac_products where visibilityid = 2)

Then you can delete from ac_products (unless there are other foreign keys.

Alternatively, if you enable cascade deletes on ac_products, you should be able to run your initial sql statement as is and sql will take care of removing all other references. However, there could still be side effects. For example, you could end up with a wishlist but no wishlistitems. I don't know if AC would have a problem with that or not.

Hope this helps.

[mazhar]

This sounds like a bug to me. I am going to report it and quick workaround for you is open your product table in design mode, then click relation ships option icon available in toolbar. This will bring a dialog with all entries of product relations to other tables. Locate ac_Products_ac_WishlistItems_FK1 relation and then change its Enforce Foreign key Constraint value to No save it. This will fix the problem.

[AbleMods]

Thanks Mazhar, wasn't sure how to deal with the key constraints - little rusty in that area.

Enabling cascade deletes would be pretty risky without knowing the full consequences - thanks for the suggestion though.

[mazhar]

Here is the bug link, you can track progress here. Let's see what Logan says about it.
http://bugs.ablecommerce.com/show_bug.cgi?id=8167

[mazhar]

In fact whishlist items relation should be very same as basket items relation with products. They both should exist independently even products are deleted from products table. I provided you the suggestion after seeing the products-basketitems relation settings.

[Logan Rhodehamel]

Some key points...

1) The database modeling tool prevents us from enforcing all constraints because in some cases it results in a circular cascade.

2) We can't enforce the foreign key on basketitems because some basket items do not have a product ID. For example, calculated taxes.

3) We can enforce the foreign key on wishlist items because only products can be placed into the wishlist. ProductId of that table cannot be null.

In this instance, if you want to manually delete from ac_Products table, it seems the safest bet is to use the nested query solution provided by ZLA above. This is how we handle the situation through code. (Although we include more tables than just ac_WishlistItems.)

[AbleMods]

ac_ProductReviews throws the same error when manually deleting products. The nested query posted by ZLA resolves the issue when used for both ac_wishlistitems and ac_productreviews