Magento Integrity constraint violation: 1062 Duplicate entry error when saving stock item?

Question

I wrote some code which adds stock data to a product. It runs fine on my testing DB. Now I started testing the code on the customers DB and this error comes up:

SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '449-1' for key 'UNQ_MAGENTO_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_STOCK_ID', query was: INSERT INTO magento_cataloginventory_stock_item (product_id, stock_id, qty, min_sale_qty, use_config_min_sale_qty, max_sale_qty, use_config_max_sale_qty, is_in_stock, low_stock_date, manage_stock, use_config_manage_stock, stock_status_changed_auto) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

This is the code:

$product->setStockData(array(
                                       'use_config_manage_stock' => 0, //'Use config settings' checkbox
                                       'manage_stock' => 1, //manage stock
                                       'min_sale_qty' => 1, //Minimum Qty Allowed in Shopping Cart
                                       'max_sale_qty' => 999, //Maximum Qty Allowed in Shopping Cart
                                       'is_in_stock' => 1, //Stock Availability
                                       'qty' => 999 //qty
                                   )
                               );

The error comes up when I am saving the product like this:

$product->save();

Without the stock code, the product can be saved. I checked the database import file I have and it has the forgeign key checks in it, so that cant be the problem. I am running Magento 1.9.

Any ideas?

Thanks!

Answer 1

The cataloginventory_stock_item table has a multiple column unique key consists of product_id and stock_id. Obviously it is named: UNQ_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_STOCK_ID and you are violating it by inserting a duplicate record where: product_id=449 and stock_id=1.

Make sure you are not violating this rule in your new records.

Anyway you can also try to replace: $product->save(); with: $product->getResource()->save($product); (As suggested here)

Answer 2

Use this query:

delete FROM cataloginventory_stock_item 
where product_id not in(SELECT entity_id FROM catalog_product_entity);

This will remove the stock of the products which have been removed.

Answer 3

I used this query:

delete FROM cataloginventory_stock_item 
where product_id not in(SELECT entity_id FROM catalog_product_entity);

and it worked for me.

Answer 4

if the error showing for the table 'cataloginventory_stock_item' having 'UNQ_CATALOGINVENTORY_STOCK_ITEM_PRODUCT_ID_STOCK_ID' index,

then please sure that you are passing different product SKU from your script each time.because if you pass the same SKU then it will fetch the id of the product on the basis of that SKU and get the duplicate entry of that particular product id.