Renaming foreign-key columns in MySQL
Solution 1
AFAIK, dropping the constraint, then rename, then add the constraint back is the only way. Backup first!
Solution 2
In case anyone is looking for the syntax it goes something like this:
alter table customer_account drop foreign key `FK3FEDF2CC1CD51BAF`;
alter table customer_account add constraint `FK3FEDF2CCD115CB1A` foreign key (campaign_id) REFERENCES campaign(id);
Solution 3
here is the SQL syntax for regular keys
ALTER TABLE `thetable`
DROP KEY `oldkey`,
ADD KEY `newkey` (`tablefield`);
Related videos on Youtube
Greg Detre
Updated on March 16, 2022Comments
-
Greg Detre about 2 years
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!
-
Ben over 9 yearspossible duplicate of How do I rename a foreign key in mysql?
-
-
BenL over 9 yearsSince MySQL 5.6.6, all foreign keys are now automatically updated when renaming a column. dev.mysql.com/doc/refman/5.6/en/alter-table.html
-
hypercube almost 9 years@BenL This is not entirely correct. The page says: "Prior to MySQL 5.6.6, adding and dropping a foreign key in the same ALTER TABLE statement may be problematic in some cases and is therefore unsupported. Separate statements should be used for each operation. As of MySQL 5.6.6, adding and dropping a foreign key in the same ALTER TABLE statement is supported for ALTER TABLE ... ALGORITHM=INPLACE but remains unsupported for ALTER TABLE ... ALGORITHM=COPY. ". Furthermore, i have seen with my own eyes MySQL 5.6.2 replacing the column name in a FK. But in mySQL 5.0.94 this is not (yet) possible.