8

i am trying to import a sql dump of magento along with some product data and i get this foreign key constraint error:

`ERROR 1452 (23000) at line 231680: Cannot add or update a child row: a foreign key constraint fails:
`magento`.`#sql-b33_27`, CONSTRAINT `FK_CATALOG_COMPARE_ITEM_CUSTOMER_ID_CUSTOMER_ENTITY_ENTITY_ID` FOREIGN KEY (`customer_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE CASCADE ON )`

This is the sql code which is causing the error :

--
-- Constraints for table `catalog_eav_attribute`
--
ALTER TABLE `catalog_eav_attribute`
  ADD CONSTRAINT `FK_CATALOG_EAV_ATTRIBUTE_ATTRIBUTE_ID_EAV_ATTRIBUTE_ATTRIBUTE_ID` FOREIGN KEY (`attribute_id`) REFERENCES `eav_attribute` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE;

I am not very comfortable with sql queries. Could some one please explain me what this query does and guide me to resolve this? Thanks.

Binod Kalathil
  • 1,939
  • 1
  • 30
  • 43
Nithin
  • 543
  • 2
  • 12
  • 28

2 Answers2

18

You are trying to add a record into catalog_eav_attribute, but you do not have a corresponding record in eav_attribute that matches on attribute_id

If you are also inserting bulk data into eav_attribute, I would recommend doing that first, and then the data would be in the table before the foreign key on catalog_eav_attribute needed to reference it.

This article discusses how you can use:

SET FOREIGN_KEY_CHECKS = 0;
--Do your update here
SET FOREIGN_KEY_CHECKS = 1;

If you cannot change the order that you are inserting data. You just have to make sure your data follows the Foreign Keys once everything has been inserted into the database, before you can re-enable the FOREIGN_KEY_CHECKS

Adam Wenger
  • 17,100
  • 6
  • 52
  • 63
  • Thanks, that did the trick. There were a few other `ALTER TABLE` queries which were resulting in a similar error but your suggestion helped me successfully import the data. Thank you. – Nithin Nov 29 '11 at 07:53
  • Thank you very much! Was trying to import a backup and had this issue! It worked for me and I've managed to import the DB successfully! – droplet May 13 '13 at 15:07
0

I used a database repair tool, after that did this in SQL:

DROP TABLE catalog_product_flat_1 , catalog_product_flat_2 , catalog_product_flat_3 ;

Now the index is built succesfully.

Max
  • 1