Hi people,
Hoping for some easy win here....
I work in a team so every now and then I have to do the house keeping on models an generate migration files for any schema changes.
I currently have a migration to run that has a significant number of instances where I change the data type of a field from INT(11) to INT(10) which references a field which I have also amended the data type accordingly.
When I run the migration however I get
------------------------
LATEST FOREIGN KEY ERROR
------------------------
120917 10:13:31 Error in foreign key constraint of table apollo/#sql-6fc_582:
FOREIGN KEY (`scheme_id` )
REFERENCES `apollo`.`tbl_limit_schemes` (`scheme_id` ),
ADD CONSTRAINT `fk_tbl_users_details_tbl_users1`
FOREIGN KEY (`user_id` )
REFERENCES `apollo`.`tbl_users` (`user_id` ):
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.
See
http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html
for correct foreign key definition.
------------------------
If I place the tbl_limit_schemes alter statement before the alter statement of the table that references it then the error shifts on to the next instance (identical issue just different tables where I'm updating the referencing table first and the referenced table after) but as there are a large number of updates in this migration this is not really a practical solution.
I foreign key checks off prior to running migration and back on afterwards which I would love to think will ignore any structural differences until alter statements are complete.
ANY help or guidance would be very much appreciated.
If you need further info I will try and supply.
Thank you kindly.