Magento Upgrade 1.9.2.3 from 1.4.0.1: Upgrade installer thrown error: Cannot add foreign key constraint

Question

Try to upgrade a magento system from 1.4.0.1 to lastest 1.9.2.3.

Getting error to Mage_Customer module's at upgrade installer file mysql4-upgrade-1.4.0.0.7-1.4.0.0.8.php

Throwing error:

a:5:{i:0;s:199:"Error in file: "app/code/core/Mage/Customer/sql/
customer_setup/mysql4-upgrade-1.4.0.0.7-1.4.0.0.8.php" - SQLSTATE[HY000]:
General error: 1215 Cannot add foreign key constraint";i:1;s:970:"
#0 app/code/core/Mage/Core/Model/Resource/Setup.php(644): Mage::exception('Mage_Core', 'Error in file: ...')

• How to resolve this.
• What is reason for error;

Answer 1

The reason :

SQLSTATE[HY000]: General error: 1215 Cannot add foreign key constraint

Actually means that the script failed to create a foreign key constraint, as the error actually stands it, but the reason for those to fail is actually kind of simple, mysql will not allow you to create a FK if the two fields you are trying to link don't have the exact same type and length.

Now from the line 31 of this update script you can indeed find a creation of a table with the addition of a foreign key constraint on the field attribute_id

$installer->run("
CREATE TABLE `{$installer->getTable('customer/form_attribute')}` (
  `form_code` char(32) NOT NULL,
  `attribute_id` smallint UNSIGNED NOT NULL,
  PRIMARY KEY(`form_code`, `attribute_id`),
  KEY `IDX_CUSTOMER_FORM_ATTRIBUTE_ATTRIBUTE` (`attribute_id`),
  CONSTRAINT `FK_CUSTOMER_FORM_ATTRIBUTE_ATTRIBUTE` FOREIGN KEY (`attribute_id`) REFERENCES `{$installer->getTable('eav_attribute')}` (`attribute_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Customer attributes/forms relations';
");

But you can also see that the field attribute_id is created as a smallest but with no length.

Since this link to the table eav_attribute you can now try to compare the field type and a field of type smallint from a dummy table that you would create.

CREATE TABLE `dummy_table` (`attribute_id` smallint UNSIGNED NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# you should now have the table named "dummy_table"

show fields from dummy_table where field = 'attribute_id';
# the Type should be "smallint(5) unsigned" and Null should be "No", if not, that is why the foreign key creation fail.
show fields from eav_attribute where field = 'attribute_id';
# the Type should be "smallint(5) unsigned" and Null should be "No"

drop table `dummy_table`;
# clean up of our testing table

The resolution :

So now, if the field eav_attribute.attribute_id is not smallint(5) unsigned not null then you can safely edit eav_attribute.attribute_id to make it smallint(5) unsigned not null.

If the field you created in your dummy table is not smallint(5) unsigned not null then just edit the line 34 of the file mysql4-upgrade-1.4.0.0.7-1.4.0.0.8.php so the field is created correctly.

  // `attribute_id` smallint UNSIGNED NOT NULL, -- old line
  `attribute_id` smallint(5) UNSIGNED NOT NULL, // new line, so the field have the right type

Answer 2

Please make sure that when exporting the original database you set Check Foreign Key Constraints to Off.

When importing this database the problem should not be there anymore.

Answer 3

This might be the issue of database engine. I also faced same kind of issue few months back so i compared my updated magento database tables engine with the 1.9 database table engine and observed that all tables have MyISAM as database engine, So I changed it as in 1.9 database and my issue got resolved.