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

Owned by Evgeniy Ermolaev (Deactivated), created
Last updated: Aug 08, 2021 by Igor Krasnykh (Unlicensed)
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:

    1. FK_BC913D0C23021919FC91247BBA30B1FE
    2. FK_CCC1AE8889A03ECB2CD24E793F468CD5

  3. We need to drop these 2 keys:

    alter table tnw_salesforce_entity_queue_relation drop foreign key FK_BC913D0C23021919FC91247BBA30B1FE; alter table tnw_salesforce_entity_queue_relation drop foreign key FK_CCC1AE8889A03ECB2CD24E793F468CD5;

  4. Add these keys with the necessary properties:

    alter table tnw_salesforce_entity_queue_relation add constraint queue_relation_queue_queue_id_fk foreign key (queue_id) references tnw_salesforce_entity_queue (queue_id) on update cascade on delete cascade; alter table tnw_salesforce_entity_queue_relation add constraint queue_relation_queue_parent_id_fk foreign key (parent_id) references tnw_salesforce_entity_queue (queue_id) on update cascade on delete cascade;

 

 

Related articles