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

Getting Error (Integrity constraint violation: 1216) after changing auto-increment number in database
 
Prattski
Member
 
Total Posts:  54
Joined:  2008-01-22
 

After finally figuring out how to get all the import stuff to work, I wanted to reset the auto-increment number for the product id in the database.  So, I changed the number in the “catalog_product_entity” table back to 1.

Now when I try to do an import, I get this error:

SQLSTATE[23000]: Integrity constraint violation: 1216 Cannot add or update a child row: a foreign key constraint fails

It seems to still import products, but I don’t like getting this error.  Any idea how I can properly fix this issue?

(I am running the latest version of Magento:  1.0.19870.1)

 
Magento Community Magento Community
Magento Community
Magento Community
 
Dave Smith
Member
 
Avatar
Total Posts:  58
Joined:  2008-02-03
Tamworth, UK
 

free bump for a man in need!

 
Magento Community Magento Community
Magento Community
Magento Community
 
rrobinson
Jr. Member
 
Avatar
Total Posts:  15
Joined:  2008-05-19
Portland, Or
 

My guess is since magento does not clear all product data when you delete the product from the admin interface that there are fk issues left over in the other tables, I would go through all the product tables, clear them and reset their autoinc values.

 
Magento Community Magento Community
Magento Community
Magento Community
 
Prattski
Member
 
Total Posts:  54
Joined:  2008-01-22
 

rrobinson - tried that already, and tried resetting all of the cache through the Magento admin as well - no luck.

 
Magento Community Magento Community
Magento Community
Magento Community
 
ally
Jr. Member
 
Total Posts:  18
Joined:  2008-05-22
 

I have been having the same problem.

So far this is the SQLthat deletes the product info that I can find. But I am certain there’s more. If you notice, the only trick I found to not get that error is to auto increment up out of where the junk exists. Hopefully someone can come up with a better idea.

TRUNCATE `catalog_product_website`;
TRUNCATE `catalog_category_product`;
TRUNCATE `catalog_product_entity`;
TRUNCATE `catalog_product_entity_datetime`;
TRUNCATE `catalog_product_entity_decimal`;
TRUNCATE `catalog_product_entity_gallery`;
TRUNCATE `catalog_product_entity_int`;
TRUNCATE `catalog_product_entity_media_gallery`;
TRUNCATE `catalog_product_entity_media_gallery_value`;
TRUNCATE `catalog_product_entity_text`;
TRUNCATE `catalog_product_entity_tier_price`;
TRUNCATE `catalog_product_entity_varchar`;

ALTER TABLE `catalog_product_website` AUTO_INCREMENT =5500;
ALTER TABLE `catalog_category_product` AUTO_INCREMENT =5500;
ALTER TABLE `catalog_product_entity` AUTO_INCREMENT =5500;
ALTER TABLE `catalog_product_entity_datetime` AUTO_INCREMENT =5500;
ALTER TABLE `catalog_product_entity_decimal` AUTO_INCREMENT =5500;
ALTER TABLE `catalog_product_entity_gallery` AUTO_INCREMENT =5500;
ALTER TABLE `catalog_product_entity_int` AUTO_INCREMENT =5500;
ALTER TABLE `catalog_product_entity_media_gallery` AUTO_INCREMENT =5500;
ALTER TABLE `catalog_product_entity_media_gallery_value` AUTO_INCREMENT =5500;
ALTER TABLE `catalog_product_entity_text` AUTO_INCREMENT =5500;
ALTER TABLE `catalog_product_entity_tier_price` AUTO_INCREMENT =5500;
ALTER TABLE `catalog_product_entity_varchar` AUTO_INCREMENT =5500;

and

DELETE FROM `core_url_rewrite`
WHERE `id_path` LIKE ‘&#xpr;oduct%’

 
Magento Community Magento Community
Magento Community
Magento Community
 
Emily
Jr. Member
 
Avatar
Total Posts:  22
Joined:  2008-03-31
Atlanta
 

I’m getting the same error message.  I tried updating the auto-increments as recommended, but I still get that error when adding or updating a new product.  I can add categories okay.  I did import products, categories, and customers from osCommerce using the magentoconnect module.  Not sure if that caused the error.

Any other suggestions?

 
Magento Community Magento Community
Magento Community
Magento Community
 
ally
Jr. Member
 
Total Posts:  18
Joined:  2008-05-22
 

I finally got my problem solved:

I reinstalled Magento, then made a backup in SQL of the database without any products. Then I added one product and made another backup. I then compared both backups and noted the differences. Essentially, I found what tables had new information added when the single product was entered, so this what I deleted. I made a delete_all products.sql file shown below and then run this in phpAdmin when I want to start over if I get import errors. You might have other tables that have to be deleted depending on the information that you are entering for your products, so this might not be perfect for you. If not, follow my steps and create your own version.

TRUNCATE `catalogindex_eav`;
TRUNCATE `cataloginventory_stock_item`;
TRUNCATE `catalog_category_product`;
TRUNCATE `catalog_product_entity`;
TRUNCATE `catalog_product_entity_datetime`;
TRUNCATE `catalog_product_entity_decimal`;
TRUNCATE `catalog_product_entity_gallery`;
TRUNCATE `catalog_product_entity_int`;
TRUNCATE `catalog_product_entity_media_gallery`;
TRUNCATE `catalog_product_entity_media_gallery_value`;
TRUNCATE `catalog_product_entity_text`;
TRUNCATE `catalog_product_entity_tier_price`;
TRUNCATE `catalog_product_entity_varchar`;
TRUNCATE `catalog_product_website`;

DELETE FROM `core_url_rewrite` WHERE `id_path` LIKE ‘&#xpr;oduct%’;

ALTER TABLE `catalogindex_eav` AUTO_INCREMENT =1;
ALTER TABLE `cataloginventory_stock_item` AUTO_INCREMENT =1;
ALTER TABLE `catalog_category_product` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_datetime` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_decimal` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_gallery` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_int` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_media_gallery` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_media_gallery_value` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_text` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_tier_price` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_entity_varchar` AUTO_INCREMENT =1;
ALTER TABLE `catalog_product_website` AUTO_INCREMENT =1;

 
Magento Community Magento Community
Magento Community
Magento Community
 
Matt Kammersell
Jr. Member
 
Avatar
Total Posts:  16
Joined:  2007-09-21
Las Vegas, NV
 

This doesn’t solve the “real problem”, you must do this each time.

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