Cannot delete or update a parent row: a foreign key constraint fails

Owned by Evgeniy Ermolaev, created
Last updated: Aug 08, 2021 by Igor Krasnykh
2 min read

 

After modules reinstallation you may face the next issue:

 

SQLSTATE[23000]: Integrity constraint violation: 1451 Cannot delete or update a parent row: a foreign key constraint fails (`cabinets_staging`.`tnw_salesforce_entity_queue_relation`, CONSTRAINT `FK_CCC1AE8889A03ECB2CD24E793F468CD5` FOREIGN KEY (`parent_id`) REFERENCES `tnw_salesforce_entity_queue` (`queue_id`) ON D), query was: INSERT INTO `tnw_salesforce_entity_queue` (`queue_id`,`entity_id`,`entity_load`,`entity_load_additional`,`entity_type`,`object_type`,`sync_type`,`sync_attempt`,`status`,`code`,`description`,`website_id`,`transaction_uid`,`additional_data`,`identify`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?), (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE `queue_id` = VALUES(`queue_id`), `entity_id` = VALUES(`entity_id`), `entity_load` = VALUES(`entity_load`), `entity_load_additional` = VALUES(`entity_load_additional`), `entity_type` = VALUES(`entity_type`), `object_type` = VALUES(`object_type`), `sync_type` = VALUES(`sync_type`), `sync_attempt` = VALUES(`sync_attempt`), `status` = VALUES(`status`), `code` = VALUES(`code`), `description` = VALUES(`description`), `website_id` = VALUES(`website_id`), `transaction_uid` = VALUES(`transaction_uid`), `additional_data` = VALUES(`additional_data`), `identify` = VALUES(`identify`)

 

The foreign key exception is related to the module re-installation.

The Magento store applied sql patches list in the patch_list table.



Actually, when we do the second time installation, Magento has our patches list in the patch_list table already and "think" all of them were applied and skip them. 

The "Foreign key SQL error" happens because the second module installation misses some foreign key properties for the tnw_salesforce_entity_queue_relation table.

Solutions

I. Correct way (recommended)

Correct the “patch_list“ table contend to allow apply the necessary patch again

  1. Remove related patch record:

    DELETE FROM patch_list WHERE patch_name = 'TNW\\Salesforce\\Setup\\Patch\\Schema\\QueueRelationForeignKeyFix';

  2. Apply patch again:

 

 

II. Alternative fix (NOT recommended):

  1. Check the tnw_salesforce_entity_queue_relation table foreign keys

    SHOW CREATE TABLE tnw_salesforce_entity_queue_relation;

  2. Analyze foreign keys data:

    CREATE TABLE `tnw_salesforce_entity_queue_relation` ( `queue_id` varchar(32) NOT NULL, `parent_id` varchar(32) NOT NULL, UNIQUE KEY `TNW_SALESFORCE_ENTITY_QUEUE_RELATION_QUEUE_ID_PARENT_ID` (`queue_id`,`parent_id`), KEY `FK_CCC1AE8889A03ECB2CD24E793F468CD5` (`parent_id`), CONSTRAINT `FK_BC913D0C23021919FC91247BBA30B1FE` FOREIGN KEY (`queue_id`) REFERENCES `tnw_salesforce_entity_queue` (`queue_id`) ON DELETE CASCADE, CONSTRAINT `FK_CCC1AE8889A03ECB2CD24E793F468CD5` FOREIGN KEY (`parent_id`) REFERENCES `tnw_salesforce_entity_queue` (`queue_id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='tnw_salesforce_entity_queue_relation'

    This table has 2 foreign keys:

  3. We need to drop these 2 keys:

  4. Add these keys with the necessary properties: