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?


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. – ypercubeᵀᴹ Jan 10 '13 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. – Michael - sqlbot Jan 10 '13 at 17:40
  • @Michael-sqlbot My mistake, I was reading the wrong row. – ypercubeᵀᴹ Jan 10 '13 at 18:19
  • But I still don't get how this will help renaming the column. – ypercubeᵀᴹ Jan 10 '13 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 ? – Melvin PRESSOUYRE Jan 10 '13 at 21:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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