DB migration scripts generated by synchronise model - big probs
Posted by: Ian Wood
Date: September 17, 2012 03:32AM

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

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.

Options: ReplyQuote

Written By
DB migration scripts generated by synchronise model - big probs
September 17, 2012 03:32AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.