When executing the following:

$product = Mage::getModel('catalog/product')->load($id);
$stock_item = $product->getStockItem();
//...other product edits...

For a very small amount of products (~13 or so) in the store (~10k SKUs) the following exception is raised:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento`.`cataloginventory_stock_item`, CONSTRAINT `FK_CATINV_STOCK_ITEM_STOCK_ID_CATINV_STOCK_STOCK_ID` FOREIGN KEY (`stock_id`) REFERENCES `cataloginventory_stock` (`stock_id`) ON DE...

Again, this is a very small number of items many of which are defunct. There have been no manual db edits, but, I'm curious if there are others that have this issue that I don't/won't know about. Is there a way to handle this or to rectify this at the db level?

  • 1
    Did you import your products automatically? This error is interessting. What (I think) happens is, that the stock-model is not loaded, normally you only have one stock (1, Default) and the product-ID is known. This stock item already exists. The question is: is it loaded with the model? And if yes, why is the ID not used for an update rather for an insert? If no, why? – Fabian Blechschmidt Feb 11 '13 at 8:56

I found that you sometimes need to initialise the stock item, otherwise I would get the Integrity constraint violation when I tried to save the product.

$stockItem = Mage::getModel('cataloginventory/stock_item')->loadByProduct($productId);
if($stockItem && !$stockItem->getProductId()) { // CHECK STOCK ITEM IS INITIALISED
    $stockItem->setData('product_id', $productId);
    $stockItem->setData('stock_id', 1);

$stockItem->setData('use_config_manage_stock', 0);
$stockItem->setData('manage_stock', 1);
$stockItem->setData('is_in_stock', 1);
$stockItem->setData('qty', 5);
| improve this answer | |

In the past when importing stock and product information I have created a separate script for products and stock. I have been successful in updating the stock information and calling save on the stock item and then continuing to work with the product.

$oStockItem = Mage::getModel('cataloginventory/stock_item');
$oStockItem->setData('is_in_stock', 1);

The assignProduct function seems to validate if the product has a stock item already assigned and will preform an update in this case.

| improve this answer | |
  • 2
    Correct - I mis-typed above. See updated answer. – philwinkle Feb 11 '13 at 15:24
  • 1
    +1 - you correctly state that saving a stock item requires calling save on the stock object itself; though, this did not resolve my actual issue which is finding broken foreign keys in the db. – philwinkle Feb 25 '13 at 17:41

I ultimately fixed this by using Magento's own db cleanup tool, which identified foreign key errors and corrected; though it did not identify causality.


| improve this answer | |
  • 2
    That almost never works. Result! – Ben Lessani - Sonassi Feb 25 '13 at 17:50
  • 1
    I am living proof that it did! Crazy! – philwinkle Feb 25 '13 at 18:01

I saw that magento was inserting '107677-1-1' in item_id which is an integer field, and it truncates values after '-', hence it finds duplicate, any idea how to stop it?

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.