Renaming foreign-key columns in MySQL

user_230523 0
Viewed 46120

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!

mysql-error-1025 foreign-keys mysql sql
7 Answers

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

user_347960

03 2022

I know this question is old, but if anyone is looking for an answer with an explanation then here it is. The code below is tested.

  1. Drop the old constrain first OldColumnConstrain
  2. Drop the old Foreign key column OldColumnName
  3. Add the new foreign key column NewColumnName with the datatype INTEGER UNSIGNED NULL
  4. (optional) I want this new foreign key after a specific column - AFTER previousColumn
  5. Constrain the new column newColumnConstrain with the reference table t1
ALTER TABLE t1
DROP FOREIGN KEY OldColumnConstrain,
DROP COLUMN OldColumnName,
ADD NewColumnName INTEGER UNSIGNED NULL AFTER previousColumn,
ADD CONSTRAINT newColumnConstrain FOREIGN KEY (NewColumnName) REFERENCES t2(id);

Important

Running the above query will rename your Old column to a New column (not exactly renaming but deleting and adding a column), but WATCHOUT that column data will be lost and it will be replaced with NULL.

Can you ACTUALLY rename your fk?

I guess not. If I find a solution I will update this answer.
See this answer https://stackoverflow.com/a/2014519/5413283

user_54132830

This task becomes simpler if you use GUI tools. I tried to rename ID column using IntelliJ IDEA Database tool and it worked like a charm! I don't have to bother about foreign keys when renaming a table or column.

See more details in IntelliJ IDEA Help | Renaming items.

MySQL 5.7

user_14293870
Related