Magento 2 - how to re-install missing foreign-key constraints?

Question

I am using a third party extension. I noticed that a constraint is missing by comparing with a fresh installation. This causes a bug when deleting an item of the extension.

Is there a command which is able to install missing constraints?

I've tried this:

1. delete entry of module from table setup_modules

php -f bin/magento setup:db-schema:upgrade
php -f bin/magento setup:db-data:upgrade
php -f bin/magento setup:upgrade

but none of these commands re-installed the missing constraint.

---

The only way I've found is deleting the table and the extension entry from the table setup_module and executing php bin/magento setup:upgrade, but this is too radical since I have to re-add the data

Answer 1

I think there is no automated way to do that in Magento.

Option 1

Run the setup:* for a fresh table and then migrate the existing data into the new tables

Option 2

If you know which foreign key constraints are missing you could manually add them with ALTER TABLE <YOUR TABLE> ADD CONSTRAINT ...

Answer 2

I developed this SQL which can be used to compare a fresh magento 2 with another database, to find missing constraints. You will have to manually create them though. But im sure there is also a way to develop a script which does this.

SELECT *
FROM information_schema.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'name_of_fresh_database'
AND CONSTRAINT_NAME NOT IN (
    SELECT CONSTRAINT_NAME
    FROM information_schema.REFERENTIAL_CONSTRAINTS
    WHERE CONSTRAINT_SCHEMA = 'name_of_other_database'
)