Renaming foreign-key columns in MySQL

Question

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!

possible duplicate of [How do I rename a foreign key in mysql?](http://stackoverflow.com/questions/6188011/how-do-i-rename-a-foreign-key-in-mysql) — Ben, Jan 09 '15 at 14:05

score 61 · Accepted Answer · answered Jan 06 '10 at 16:41

61

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

answered Jan 06 '10 at 16:41

Dave Swersky

34,502
9
78
118

11

Since MySQL 5.6.6, all foreign keys are now automatically updated when renaming a column. http://dev.mysql.com/doc/refman/5.6/en/alter-table.html – BenL Jan 07 '15 at 17:53
4

@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. – hypercube May 28 '15 at 12:55

score 29 · Answer 2 · answered Feb 19 '13 at 19:13

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);

score 3 · Answer 3 · edited Dec 08 '12 at 23:30

3

here is the SQL syntax for regular keys

ALTER TABLE `thetable`
  DROP KEY `oldkey`, 
  ADD KEY `newkey` (`tablefield`);

edited Dec 08 '12 at 23:30

bradoaks

1,112
10
12

answered Sep 28 '12 at 15:56

monika mevenkamp

627
6
11

score 1 · Answer 4 · edited Feb 01 '18 at 03:42

Expanding on @Dewey's answer, here's a little script to rename FKs generated by Hibernate in a useful manner ("FK__" + table name + "__" + referenced table name).

SELECT CONCAT(
  "alter table ", TABLE_NAME, " drop foreign key ", CONSTRAINT_NAME,";\n",
  "alter table ", TABLE_NAME, " drop key ", CONSTRAINT_NAME, ";\n",
  "alter table ", TABLE_NAME, " add key FK__", table_name, "__",
      referenced_table_name, " (", column_name, ");\n",
  "alter table ", TABLE_NAME, " add constraint FK__", table_name, "__",
      referenced_table_name , " foreign key (", column_name, ") ",
      "references ", referenced_table_name,
      "(", referenced_column_name, ");"
  ) AS runMe 
FROM
  information_schema.key_column_usage
WHERE 
  TABLE_SCHEMA='myschemaname' 
  AND 
  constraint_name like 'FK_%';

A bit of output:

alter table visitor_browsers drop foreign key FK_4ygermmic4fujggq1kp96dx47;
alter table visitor_browsers drop key FK_4ygermmic4fujggq1kp96dx47;
alter table visitor_browsers add key FK__visitor_browsers__websites (website);
alter table visitor_browsers add constraint FK__visitor_browsers__websites foreign key (website) references websites(id);

score 0 · Answer 5 · edited Feb 01 '18 at 03:42

0

The following query will build the correct syntax automatically. Just execute each line returned and all your FKEYs will be gone.

I leave the reverse (adding them back) as an exercise for you.

SELECT CONCAT("alter table ", TABLE_NAME," drop foreign key `", CONSTRAINT_NAME,"`; ") AS runMe
FROM information_schema.key_column_usage 
WHERE TABLE_SCHEMA='MY_SCHEMA_NAME';

edited Feb 01 '18 at 03:42

Iharob Al Asimi

52,653
6
59
97

answered Apr 12 '15 at 14:29

Dewey

756
6
17

score 0 · Answer 6 · answered Mar 15 '22 at 23:46

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.

Drop the old constrain first OldColumnConstrain
Drop the old Foreign key column OldColumnName
Add the new foreign key column NewColumnName with the datatype INTEGER UNSIGNED NULL
(optional) I want this new foreign key after a specific column - AFTER previousColumn
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

score -2 · Answer 7 · answered Nov 22 '18 at 07:48

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

Renaming foreign-key columns in MySQL

7 Answers7

03 2022

Important

Can you ACTUALLY rename your fk?

Linked

Related