Integrity constraint violation: 1452 Cannot add or update a child row
Occasionally for one reason or another the Magento indexer my become stuck and needs a helping hand to push through some system errors. For example an API product update through a Product Information Manager (PIM) may cause an issue when updating a single or batch of products and prevent changes from displaying which should be visible on your website.
You may find errors such as these in your log file (var/log/exception.log) or when you run the indexer manually through the command prompt.
Product Attributes index was rebuilt successfully in 00:00:09
Product Flat Data index process unknown error:
exception ‘PDOException’ with message ‘SQLSTATE: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`database., CONSTRAINT `FK_CAT_PRD_FLAT_1_ENTT_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`entity_id`) REFERENCES `catalog_)’ in /var/www/html/magento.dev/lib/Zend/Db/Statement/Pdo.php:236
Category Flat Data index was rebuilt successfully in 00:00:00
Category Products index was rebuilt successfully in 00:00:04
Catalog Search Index index was rebuilt successfully in 00:00:02
Tag Aggregation Data index was rebuilt successfully in 00:00:00
Default Values (MANAdev) index was rebuilt successfully in 00:00:00
Fortunately there is a solve. You can truncate the catalog_product_flat_1 table which is fine for low traffic sites.
We do not recommend this, particularly if you are in the middle of a flash sale and support a high traffic site.
Preferably you can run the query below to find the offending entity_id and remove it fromt he catalog_product_flat_1 table.
FROM catalog_product_flat_1 pf1
LEFT JOIN catalog_product_entity p ON pf1.entity_id = p.entity_id
WHERE ISNULL( p.entity_id )
Delete the entity_id from catalog_product_flat_1
You should be able to now re-index your website!