I need to modify a foreign key related column. When I tried to alter it I got "Error on rename of 'x' to 'y' (errno: 150)" error. So I googled and found foreign key relation is the 'villain'. I dropped the foreign key relation, altered the column and created foreign key relation.

Is there any way to alter foreign key related columns without dropping the relation?

  • 2
    I believe MySQL does not support that.
    – user1822
    Commented Jan 10, 2013 at 7:21

1 Answer 1


Yeah i think you have a SQL command like :

SET foreign_key_checks = 0;  

which should be session based. Then you set =1 once you're done.

  • foreign_key_checks is a global (only) server variable. Commented Jan 10, 2013 at 16:41
  • foreign_key_checks is both global and session, so the example will work and will only impact the current session. mysqldump writes these repeatedly to backup files so foreign key constraints don't interfere with restoration. Commented Jan 10, 2013 at 17:40
  • @Michael-sqlbot My mistake, I was reading the wrong row. Commented Jan 10, 2013 at 18:19
  • But I still don't get how this will help renaming the column. Commented Jan 10, 2013 at 19:48
  • From what I understood Praveen wants to do some work on a column that has a foreign key constraint, and that without actually dropping the constraint; but maybe I got this wrong ? Commented Jan 10, 2013 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.