Backstory:
I was working late to get a feature “working” before heading off to sleep. Somewhere in between autocomplete and matching definitions to Magento core files I had prepped my setup script. But upon running php bin/magento setup:upgrade instead of being all set I was greeted with a foreign key constraint issue to debug.
->addForeignKey(
$setup->getFkName(self::CUSTOMMODULE_STORE_PIVOT_NAME, 'store_id', 'store', 'store_id'),
'store_id',
$setup->getTable('store'),
'store_id',
\Magento\Framework\DB\Ddl\Table::ACTION_CASCADE
)So I went back to UpgradeSchema.php to take a closer look at things. I compared what I’d written to vendor/magento/module-store/Setup/InstallSchema.php since that’s the table I was creating my constraint agaisnt and realized that I’d set store_id on my pivot table to TYPE_INTEGER where it should have been TYPE_SMALLINT.
->addColumn(
'store_id',
\Magento\Framework\DB\Ddl\Table::TYPE_SMALLINT, //<-- I assumed this was going to be an integer, oops.
null, ['unsigned' => true, 'nullable' => false, 'primary' => true],
'Store ID'
)Simple mistake, simple fix. I updated the type definition, re-ran setup, and was back in business. But the output was not that helpful and I was curious how to get better information about what actually went wrong in the event it wasn’t as apparent. And after a quick search here we are, log in to mysql from CLI and check the INNODB Engine Status logs.
Debugging:
ERROR 1215 (HY000): Cannot add foreign key constraint
mysql -u whomever -p
mysql> SHOW ENGINE INNODB STATUS;The piece of the log we’re interested in for this use case is the aptly named latest foreign key error section.
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-05-08 21:57:58 0x700001222000 Error in foreign key constraint of table ...
...
//The table name and query are output here but not relevant as it was output when the error was thrown during setup:upgrade. See below for the real issue:
...
Cannot find an index in the referenced table where the
referenced columns appear as the first columns, or column types
in the table and the referenced table do not match for constraint.
Note that the internal storage type of ENUM and SET changed in
tables created with >= InnoDB-4.1.12, and such columns in old tables
cannot be referenced by such columns in new tables.
Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-foreign-key-constraints.html for correct foreign key definition.
TLDR; double check your column definitions. Make sure the data type and attributes match where appropriate. For example: This DOES mean both ID columns should be unsigned but DOES NOT mean your pivot table’s ID column should be auto incrementing. Put this in your memory bank and hopefully you’ll find it useful while debugging at some point.
SO to a stackoverflow answer from 2013 for still being helpful – https://stackoverflow.com/questions/15534977/mysql-cannot-add-foreign-key-constraint
More articles/tips/tool for debugging here.