Posting in the Magento forums has been disabled pending the implementation of a new and improved forum solution which should better serve the community.

For new questions please post at magento.stackexchange.com, the community-run support site for the Magento community. We will be providing updates on the new forum solution soon. For questions or concerns please email community@magento.com.

Magento Forum

Page 1 of 5
[FIXED] When saving a product there is an SQLSTATE[23000]: Integrity constraint violation error message
 
Lenny2
Sr. Member
 
Total Posts:  171
Joined:  2008-05-21
München
 

After updating to 1.1. there is the following error message when trying to save a product in the backend:

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`store/catalog_product_website`, CONSTRAINT `FK_CATAOLOG_PRODUCT_WEBSITE_WEBSITE` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE)

Any ideas??

Image Attachments
error_sqlstate.jpg
 
Magento Community Magento Community
Magento Community
Magento Community
 
cbourgon
Jr. Member
 
Total Posts:  22
Joined:  2008-06-19
 

Same here!

SQLSTATE[23000]Integrity constraint violation1452 Cannot add or update a child rowa foreign key constraint fails (`clabou_magento/catalog_product_website`, CONSTRAINT `FK_CATAOLOG_PRODUCT_WEBSITE_WEBSITEFOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCA)

Also getting similar error when editing existing problems.

Any help would be very much appreciated

 
Magento Community Magento Community
Magento Community
Magento Community
 
cbourgon
Jr. Member
 
Total Posts:  22
Joined:  2008-06-19
 

Are we the only two having this problem?

Anyone have any idea what might be the cause?

 
Magento Community Magento Community
Magento Community
Magento Community
 
redpen
Sr. Member
 
Total Posts:  254
Joined:  2008-01-23
 

I’m getting this when trying to save a Category. 

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento/catalog_category_product_index`, CONSTRAINT `FK_CATALOG_CATEGORY_PRODUCT_INDEX_PRODUCT_ENTITY` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON D)

 
Magento Community Magento Community
Magento Community
Magento Community
 
cbourgon
Jr. Member
 
Total Posts:  22
Joined:  2008-06-19
 

Might this possibly linked to the PDOException Object error that we got upon upgrading?

Is there something screwed up in our databases now?

 
Magento Community Magento Community
Magento Community
Magento Community
 
redpen
Sr. Member
 
Total Posts:  254
Joined:  2008-01-23
 
mbourgon - 29 July 2008 07:51 AM

Might this possibly linked to the PDOException Object error that we got upon upgrading?

Is there something screwed up in our databases now?

Possibly, although I’d have thought the DB stuff would have been OK…

Would be nice to hear from Magento on this…

 
Magento Community Magento Community
Magento Community
Magento Community
 
redpen
Sr. Member
 
Total Posts:  254
Joined:  2008-01-23
 

Right, as ever we’re left to fix these things ourselves.  Here’s the solution to my problem, might help others out.

This SQL:

SELECT
  
*
FROM
  catalog_category_product_index
LEFT
  JOIN
    catalog_product_entity
    ON catalog_category_product_index
.product_id catalog_product_entity.entity_id
WHERE
  catalog_product_entity
.entity_id IS NULL

Highlighted corrupt product referencess in the ‘catalog_category_product_index’ table, which had to be manually deleted from within PHPMyAdmin.

Do the same with ‘catalog_category_product’ and you should be good to go, until the tables get corrupt again, of course.

 
Magento Community Magento Community
Magento Community
Magento Community
 
cbourgon
Jr. Member
 
Total Posts:  22
Joined:  2008-06-19
 

It seems that for the foreign key constraint command (that the store implementing in this situation) both tables need to be of type InnoDB.

See this link for details.

It seems that for the error that Lenny2 and I were having with the catalog_product_website table was caused, at least for me, by the fact that the catalog_product_website table was of type InnoDB, but the table that it was referencing to get the foreign key, core_website was of a different type. I changed the core_website table to type InnoDB (was type of MyISAM) and that seems to have fixed that error. Here is the SQL command:

ALTER TABLE core_website ENGINE=INNODB

Please make sure to backup your database before editing your tables manually.

Now I’m getting the same error as Redpen. I’m going to look into that now…

 
Magento Community Magento Community
Magento Community
Magento Community
 
cbourgon
Jr. Member
 
Total Posts:  22
Joined:  2008-06-19
 

Ok so I found that the error that both redpen and I were having

redpen -

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`magento/catalog_category_product_index`, CONSTRAINT `FK_CATALOG_CATEGORY_PRODUCT_INDEX_PRODUCT_ENTITY` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON D)

is cause by the same thing as the one that Lenny2 and I were first experiencing

Lenny2 -

SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`store/catalog_product_website`, CONSTRAINT `FK_CATAOLOG_PRODUCT_WEBSITE_WEBSITE` FOREIGN KEY (`website_id`) REFERENCES `core_website` (`website_id`) ON DELETE CASCADE ON UPDATE CASCADE)

As I said in my post above, the foreign key constraint command requires that both the table that it is being applied to, and the table in which it is referencing the foreign key, must be of type InnoDB.

The catalog_category_product_index in my database was of type InnoDB but the table it was referencing to get the foreign key, catalog_product_entity was of type MyISAM. Once I change the catalog_product_entity table to type InnoDB the error disappeared and I can now create products and categories. I did this with the following command.

ALTER TABLE catalog_product_entity ENGINE=INNODB

Please make sure you backup your database before you mess with it.

 
Magento Community Magento Community
Magento Community
Magento Community
 
adimagento
Guru
 
Avatar
Total Posts:  626
Joined:  2007-08-29
Los Angeles, CA
 

Please check if your tables are either MyISAM or InnoDB - if they are MyISAM please run this attached file in order to fix the problem.

File Attachments
innodb-fix.sql  (File Size: 15KB - Downloads: 2556)
 
Magento Community Magento Community
Magento Community
Magento Community
 
redpen
Sr. Member
 
Total Posts:  254
Joined:  2008-01-23
 

Not sure why this is marked as ‘fixed’ - just had the same problem surface again.  Seems like the DB slowly gets corrupted during normal usage, had to manually go in and remove ‘stray’ records from the DB via PHPMyAdmin… =(

 
Magento Community Magento Community
Magento Community
Magento Community
 
redpen
Sr. Member
 
Total Posts:  254
Joined:  2008-01-23
 

And it’s just done it again.  Can’t create/edit categories.

Any word on when this will be fixed?

 
Magento Community Magento Community
Magento Community
Magento Community
 
russell2pi
Member
 
Total Posts:  31
Joined:  2008-07-19
 

Same problem here, thanks redpen for the temporary “fix”.

Also I lost a product—the product ID with corrupt references was also missing from the product list in admin. Sigh.

Beginning to think I should have gone with oscommerce. The silence from the developers is deafening.

A little bit of debugging info-- the offending product was one I don’t believe I went through with saving. I think I changed my mind after going past the first screen (attribute set choice) and after filling out some of the attribute groups.  It’s also possible I did save it, then deleted it. So it seems that either an incomplete addition to the catalogue or a deletion of a product can trigger the corruption of the database.

 
Magento Community Magento Community
Magento Community
Magento Community
 
dotprkirk
Member
 
Total Posts:  50
Joined:  2008-04-22
 

Any update on this? My tables are innodb, I’ve run the fixes posted, I’m still unable to create categories without the “Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails” error popping up.

 
Magento Community Magento Community
Magento Community
Magento Community
 
redpen
Sr. Member
 
Total Posts:  254
Joined:  2008-01-23
 
dotprkirk - 26 August 2008 06:43 AM

Any update on this? My tables are innodb, I’ve run the fixes posted, I’m still unable to create categories without the “Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails” error popping up.

Check the roadmap fixes to see if this is being addressed.

I’ve just had this problem again this morning.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Gabrusch
Member
 
Avatar
Total Posts:  44
Joined:  2008-03-26
Wuerzburg, Germany
 
redpen - 30 July 2008 12:53 AM

Right, as ever we’re left to fix these things ourselves.  Here’s the solution to my problem, might help others out.

This SQL:

SELECT
  
*
FROM
  catalog_category_product_index
LEFT
  JOIN
    catalog_product_entity
    ON catalog_category_product_index
.product_id catalog_product_entity.entity_id
WHERE
  catalog_product_entity
.entity_id IS NULL

Highlighted corrupt product referencess in the ‘catalog_category_product_index’ table, which had to be manually deleted from within PHPMyAdmin.

Do the same with ‘catalog_category_product’ and you should be good to go, until the tables get corrupt again, of course.

redpen can you explain this al little more detaild as I get the same error and get results for this query

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top
Page 1 of 5