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

SQL Error: “Integrity constraint violation” with ‘catalog_product_website’ and ‘core_website’ tables
 
abovethefold
Jr. Member
 
Total Posts:  13
Joined:  2008-03-16
Boston, MA
 

I’m experiencing an error when I try to update a product in the catalog. All of the products were imported successfully into Magento using the osCommerce import tool but the products cannot be updated unless I do it manually in MySQL. The error I get is:

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

I am attaching screenshots of the structure and contents of the two tables in question. Are there any changes I need to make to these tables in order to fix the problem?

Image Attachments
core_website-structure.jpgcatalog_product_website_structure.jpgcatalog_product_website-browse.jpgcore_website-browse.jpg
 
Magento Community Magento Community
Magento Community
Magento Community
 
brendanb
Mentor
 
Total Posts:  1093
Joined:  2008-07-16
London, United Kingdom
 

hey there,

i dont know anything about the oscomm import tool, but you might want to check this post of you havent about the ER diagram. This might give you some ideas

http://www.magentocommerce.com/boards/viewthread/3375/#t25564

hth
brendan

 
Magento Community Magento Community
Magento Community
Magento Community
 
abovethefold
Jr. Member
 
Total Posts:  13
Joined:  2008-03-16
Boston, MA
 

Thanks for the reply, brendan. Unfortunately, that post did not help.

 
Magento Community Magento Community
Magento Community
Magento Community
 
keadie
Jr. Member
 
Total Posts:  7
Joined:  2008-08-14
 

Bump.  Anybody have a solution to this problem yet or any other information.  I am receiving the same message.  I love Magento, whoever it still seems a little to buggy for production...*ducks*.  My test website glitched really bad a week ago and had to start from scratch.

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

 
Magento Community Magento Community
Magento Community
Magento Community
 
Juxecl
Member
 
Total Posts:  37
Joined:  2007-12-08
 

Check which website_id values are stored in the core_website table. (Probably will be 0 and 1)

Check the website_id values in the catalog_product_website table. My guess is that there is a website_id value which isn’t in the core_website table (going by the screenshots in the first post).

Quick way to check:

SELECT COUNT(*) 
FROM catalog_product_website
WHERE website_id NOT IN

    
SELECT website_id
    FROM core_website
)
This SQL query should return 0, if not then there are that number of incorrect rows.
 
Magento Community Magento Community
Magento Community
Magento Community
 
keadie
Jr. Member
 
Total Posts:  7
Joined:  2008-08-14
 

Thanks for the response Rowd!  I checked that earlier and also made sure all the tables are INNODB.  All the values in the catalog_product_website table are 1 and all products are in that table.  Thanks for the support.

 
Magento Community Magento Community
Magento Community
Magento Community
 
keadie
Jr. Member
 
Total Posts:  7
Joined:  2008-08-14
 

Well, after turning on query logging in mysql I have found the exact query that is causing the problem, at least in my case.  It doesn’t completely make sense though.

The previous query pulls out the website id for the related product.

SELECT `catalog_product_website`.* FROM `catalog_product_website` WHERE (product_id=’3595’)

The next query (the problem one) trys to insert a new record into the catalog_product_website table with a website_id that does not exist

INSERT INTO `catalog_product_website` (`product_id`, `website_id`) VALUES (’3595’, ‘2’)

Don’t know where the code is getting the 2 from as my entries only show 0 & 1.  I’ll keep everyone posted if any else is having the same problem.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Gabrusch
Member
 
Avatar
Total Posts:  44
Joined:  2008-03-26
Wuerzburg, Germany
 

So this would mean that you’d be able to alter it manually in MySql.

Valuable hint anyway, thanks

 
Magento Community Magento Community
Magento Community
Magento Community
 
Sebastian G
Guru
 
Total Posts:  336
Joined:  2007-09-15
 

@ keadie:  This is an issue that only occurs by not having a default store id and it’s planned to be resolved for the upcoming 1.1.5 release smile

 
Magento Community Magento Community
Magento Community
Magento Community
 
keadie
Jr. Member
 
Total Posts:  7
Joined:  2008-08-14
 

Thanks Tomas G,

How do you set that.  I have tried several ways including:

On the index page setting Mage::run(’default’);
Under manage stores there is only one option for default store and it was selected.
Have been looking through the XML etc/config files to find a way

Thanks!

- Kyle

 
Magento Community Magento Community
Magento Community
Magento Community
Magento Community
Magento Community
Back to top