mysql - Magento: a foreign key constraint fails after update 1.4 -> 1.7

Question

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (my_magento/catalog_category_product_index, CONSTRAINT FK_CAT_CTGR_PRD_IDX_CTGR_ID_CAT_CTGR_ENTT_ENTT_ID FOREIGN KEY (category_id) REFERENCES catalog_category_entity (entity_id) O)

This is the error I'm getting when trying to reindex data (outputed it as it's adviced here: "There was a problem??? with reindexing process." after product import )

But that solution doesn't help at all. Please help where to search for it, I'm not really familiar with MySQL foreign keys. How to detect corrupted records? What's the principe of fixing them?

Answer 1

One of your products is referencing to a category_id which does not exist. Usually this should not happen when editing things through the admin-backend. Did you delete the category directly in the database?

You have to check which product is referencing the category and remove that reference. The references between category and product are stored in catalog_category_product. The categories in catalog_category_entity. To find the row you can use that query:

SELECT * FROM catalog_category_product ccp
LEFT JOIN catalog_category_entity cce ON ccp.category_id = cce.entity_id
WHERE cce.entity_id IS NULL