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

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

Remove related patch record:

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

Apply patch again:
The follwing commands are usually executed for each Magento deployment.
```
bin/magento setup:upgrade
bin/magento setup:di:compile
bin/magento indexer:reindex
```
Re-deploy static content
```
rm -rf pub/static/frontend/ pub/static/deployed_version.txt  pub/static/adminhtml/
bin/magento setup:static-content:deploy -f
```
Clear / Clean Magento cache
```
bin/magento cache:clean
```
Last but not least, disable the Magento maintenance mode
```
bin/magento maintenance:disable
```
If you run into any issues, try executing the following command and try again
rm -rf var/generation/* generated/*

II. Alternative fix (NOT recommended):

Click here to expand...

Check the tnw_salesforce_entity_queue_relation table foreign keys
```
SHOW CREATE TABLE tnw_salesforce_entity_queue_relation;
```

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:

```
FK_BC913D0C23021919FC91247BBA30B1FE
```
```
FK_CCC1AE8889A03ECB2CD24E793F468CD5
```

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;

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;

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

Solutions

I. Correct way (recommended)

II. Alternative fix (NOT recommended):

Related articles