Foreign key constraint error 1452 in MySQL - Magento import

15,752

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

Share:
15,752
Nithin
Author by

Nithin

Updated on June 04, 2022

Comments

  • Nithin
    Nithin almost 2 years

    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.

  • Nithin
    Nithin over 12 years
    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.
  • droplet
    droplet almost 11 years
    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!