Renaming foreign-key columns in MySQL

sql mysql foreign-keys mysql-error-1025
Greg Detre picture Greg Detre · Jan 6, 2010 · Viewed 40.7k times · Source

We're trying to rename a column in MySQL (5.1.31, InnoDB) that is a foreign key to another table.

At first, we tried to use Django-South, but came up against a known issue:

http://south.aeracode.org/ticket/243

OperationalError: (1025, "Error on rename of './xxx/#sql-bf_4d' to './xxx/cave_event' (errno: 150)")

AND

Error on rename of './xxx/#sql-bf_4b' to './xxx/cave_event' (errno: 150)

This error 150 definitely pertains to foreign key constraints. See e.g.

What does mysql error 1025 (HY000): Error on rename of './foo' (errorno: 150) mean?

http://www.xaprb.com/blog/2006/08/22/mysqls-error-1025-explained/

So, now we're trying to do the renaming in raw SQL. It looks like we're going to have to drop the foreign key first, then do the rename, and then add the foreign key back again. Does that sound right? Is there a better way, since this seems pretty confusing and cumbersome?

Any help would be much appreciated!

Answer

Dave Swersky picture Dave Swersky · Jan 6, 2010

AFAIK, dropping the constraint, then rename, then add the constraint back is the only way. Backup first!

Related questions

MySQL Cannot Add Foreign Key Constraint

So I'm trying to add Foreign Key constraints to my database as a project requirement and it worked the first time or two on different tables, but I have two tables on which I get an error when trying to …

Read More
How to create relationships in MySQL

In class, we are all 'studying' databases, and everyone is using Access. Bored with this, I am trying to do what the rest of the class is doing, but with raw SQL commands with MySQL instead of using Access. I …

Read More
Can table columns with a Foreign Key be NULL?

I have a table which has several ID columns to other tables. I want a foreign key to force integrity only if I put data in there. If I do an update at a later time to populate that column, …

Read More