Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question
I believe MySQL does not support that. – a_horse_with_no_name Jan 10 '13 at 7:21
up vote 1 down vote accepted

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.

share|improve this answer
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 posting your answer, you agree to the privacy policy and terms of service.

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