Foreignkey integrity error while configurable product import magento 2

Question

When I was trying to import some configurable products, magento admin shows the following error

General system exception happened
Additional data: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails 
(`magento2`.`catalog_product_website`, CONSTRAINT `CAT_PRD_WS_PRD_ID_CAT_PRD_ENTT_ENTT_ID` FOREIGN KEY (`product_id`) REFERENCES `catalog_product_entity` (`entity_id`) ON DELETE CASCADE), query was: INSERT INTO `catalog_product_website` (`product_id`,`website_id`) VALUES (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?), (?, ?) ON DUPLICATE KEY UPDATE `product_id` = VALUES(`product_id`), `website_id` = VALUES(`website_id`)

But the configurable product csv successfully passed the first validation check in the admin (Check data button). But when we click the import in next step, the above error is showing.

But the product information is inserted in the "catalog_product_entity" table. When I check the product detail page in admin, the configurable product variations are not listing and that product is not listing in store view.

Answer 1

I was able to solve this problem by shortening my SKUs. I had many that were over the 64 character limit imposed by the database schema and shortening them allowed for a successful import.

It is worth mentioning that the Data Checker does not currently (Oct. 2018) check for SKU length.

Answer 2

Check the list of SKU's in your CSV file. Usually this means there are invalid characters in the list.

If you go back to System > Import, and use Delete instead of Add/Update, and instruct it to STOP ON ERRORS, after you check your file it will warn you that products x - x do not exist, this indicated where the last operation fails.

Now open the CSV you are using, go to the line number magento just told you had it had a problem deleting, and you should see what caused your initial error in that line.

After that is fixed, just retry the transaction (Go back to System > Import, recheck the data, then hit the import button). Happens once in a while!

Answer 3

I was getting the same error but I wasn't willing to shorten my SKUs. So what I did was change the default sku length across the system to 255.

Specifically, the changes I made are:

• Magento code: Change const SKU_MAX_LENGTH = 64 to const SKU_MAX_LENGTH = 255 in the file: module-catalog/Model/Product/Attribute/Backend/Sku.php
• Database: Ran the following commands to update the length of the sku field in certain tables involved in the import process:
  • ALTER TABLE `catalog_product_entity` CHANGE `sku` `sku` VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT 'SKU';
  • UPDATE `eav_attribute` SET `frontend_class` = 'validate-length maximum-length-255' WHERE `eav_attribute`.`attribute_id` = 74; *the attribute_id = 74 refers to the sku field

The commands above are taken from phpmyadmin.

It is also worth mentioning that the changes mentioned above are meant to only allow products to be imported via a csv file. I haven't thoroughly searched all the tables in the database so there may be other references to the sku field which will also need to be updated.