Cannot add or update a child row
The following error is encountered in the Admin panel after MageRewards has been installed or updated: "Cannot add or update a child row: a foreign key constraint fails (`MAGENTO_DB`., CONSTRAINT `FK_TRANSFER_CUSTOMER_ID` FOREIGN KEY (`customer_id`) REFERENCES `mage_customer_entity` (`entity_id`)"
This can also happen when rebuilding the customer points index in the admin panel. The exception thrown in this case looks like this:
SQLSTATE: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails (`MAGENTO_DB`.`rewards_customer_index_points`, CONSTRAINT `FK_CUSTOMER_INDEX_POINTS_CUSTOMER_ID` FOREIGN KEY (`customer_id`) REFERENCES `customer_entity` (`entity_id`) ON DELETE CASCADE ON UP)'
In MageRewards v18.104.22.168 some additional constraints were added to the database to improve the performance optimization. One constraint added to the database was a Foreign Key constraint on the MageRewards point transfer records against the customer records in Magento’s database. This addition makes the lookup of point transfers by customer accounts more efficient.
The message you're experiencing is actually complaining that it cannot apply the Foreign Key constraint link in place on your database. This is because your MageRewards transfer records contain entries for customer accounts that no longer exist in Magento. These are customer accounts that were either deleted, or the accounts were missed during a Magento database migration.
You can verify whether there are missing records by running a manual SQL query directly on your Magento database. This manual query will generate a list of missing customer IDs, that is, customer records that MageRewards is expecting to find.
The following is the script that you can run on your database that will extract a list of missing customer IDs:
<em> Select DISTINCT transfers.`customer_id` from `rewards_transfer` as transfers where transfers.`customer_id` Not IN ( Select customers.`entity_id` from `customer_entity` as customers );</em>
We just modified MageRewards 22.214.171.124 to fix this for you automatically. You should re-download MageRewards 126.96.36.199 and uploaded it again to your FTP server.Once you do this, visit the admin panel, and look for the "Re-install Database" button in the Configuration screen as illustrated in the screenshot below. Refresh your Magento Cache and you're good to go!
If you choose not to have MageRewards do this automatically, you can fix this manually.
You have two options to fix this manually:
- Option 1: Make sure the customer table is up-to-date and includes the accounts that the above query reported as missing. This is practical if the accounts were missed during a migration for example or that the accounts were accidentally deleted.
- Option 2: If the accounts were intentionally deleted, the logical course of action would be to delete the associated point transfers for the missing customers, which includes relevant records for the “rewards_transfer” and "rewards_transfer_reference" table. Running the following SQL queries on your database will automatically delete these orphaned transfers (this cannot be undone!):
<em>Delete from `rewards_transfer` where `rewards_transfer`.`customer_id` NOT IN ( Select customers.`entity_id` from `customer_entity` as customers ); Delete from `rewards_customer_index_points` where `rewards_customer_index_points`.`customer_id` NOT IN ( Select customers.`entity_id` from `customer_entity` as customers );</em>
Please be sure to make appropriate backups before making any modifications to your database.
- Once the orphaned transfers and their reference records have been eliminated, you should re-run the installation process of MageRewards through the "Re-install database" option in the backend (see screenshot attached).