Integrity constraint violation: Duplicate entry for key ‘UNQ_CATALOGINVENTORY_STOCK_ITEM PRODUCT_ID_STOCK_ID’

The reason you are seeing this error may vary, but in my case I had been working with a custom osCommerce to Magento import. At some point I disabled handling stock in the admin panel. It would make sense for Magento to truncate the table containing stock item data, right? Well, they don’t do that.

It’s funny because all other tables pertaining to stock are emptied. On my product import I was still setting stock options (which should NOT be a problem, since you should have the option to change your mind). So naturally MySQL complained that the key already existed.

Normally I will never use hard SQL to work with Magento since it causes so many problems, but Magento left me with no ability to remove the items.

1
2
3
4
$catInv = Mage::getModel('cataloginventory/stock');
$stock  = $catInv->getItemCollection();
 
Zend_Debug::dump(count($stock));
$catInv = Mage::getModel('cataloginventory/stock');
$stock  = $catInv->getItemCollection();

Zend_Debug::dump(count($stock));

This returns zero, so I wasn’t able to access the delete handler. No matter, this is a fairly simple fix so it won’t hurt to use hard SQL. Note this is for Magento 1.6.1, and that your database schema (past or future) may not work:

1
 TRUNCATE cataloginventory_stock_item;
 TRUNCATE cataloginventory_stock_item;

The import worked for me afterward, no further problems.

Discussion

Leave a Reply