重命名 MySQL 中的外键列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2014498/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Renaming foreign-key columns in MySQL
提问by Greg Detre
We're trying to rename a column in MySQL (5.1.31, InnoDB) that is a foreign key to another table.
我们试图重命名 MySQL (5.1.31, InnoDB) 中的一个列，该列是另一个表的外键。
At first, we tried to use Django-South, but came up against a known issue:
起初，我们尝试使用 Django-South，但遇到了一个已知问题：
http://south.aeracode.org/ticket/243
http://south.aeracode.org/ticket/243
OperationalError: (1025, "Error on rename of './xxx/#sql-bf_4d' to './xxx/cave_event' (errno: 150)")
OperationalError: (1025, “将‘./xxx/#sql-bf_4d’重命名为‘./xxx/cave_event’时出错(errno: 150)”）
AND
和
Error on rename of './xxx/#sql-bf_4b' to './xxx/cave_event' (errno: 150)
将“./xxx/#sql-bf_4b”重命名为“./xxx/cave_event”时出错（错误号：150）
This error 150 definitely pertains to foreign key constraints. See e.g.
此错误 150 肯定与外键约束有关。见例如
What does mysql error 1025 (HY000): Error on rename of './foo' (errorno: 150) mean?
mysql error 1025 (HY000): Error on rename of './foo' (errorno: 150) 是什么意思？
http://www.xaprb.com/blog/2006/08/22/mysqls-error-1025-explained/
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?
所以，现在我们正在尝试在原始 SQL 中进行重命名。看起来我们必须先删除外键，然后进行重命名，然后再次添加外键。听起来对吗？有没有更好的方法，因为这看起来很混乱和麻烦？
Any help would be much appreciated!
任何帮助将非常感激！
回答by Dave Swersky
AFAIK, dropping the constraint, then rename, then add the constraint back is the only way. Backup first!
AFAIK，删除约束，然后重命名，然后重新添加约束是唯一的方法。先备份！
回答by 34m0
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);
回答by monika mevenkamp
here is the SQL syntax for regular keys
这是常规键的 SQL 语法
ALTER TABLE `thetable`
DROP KEY `oldkey`,
ADD KEY `newkey` (`tablefield`);
回答by Dewey
The following query will build the correct syntax automatically. Just execute each line returned and all your FKEYs will be gone.
以下查询将自动构建正确的语法。只需执行返回的每一行，您所有的 FKEY 就会消失。
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';
回答by Jan ?ankowski
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).
扩展@Dewey 的回答，这里有一个小脚本，用于以有用的方式重命名 Hibernate 生成的 FK ("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);
回答by naXa
This task becomes simpler if you use GUI tools. I tried to rename ID column using IntelliJ IDEA Database tooland it worked like a charm! I don't have to bother about foreign keys when renaming a table or column.
如果您使用 GUI 工具，此任务会变得更简单。我尝试使用 IntelliJ IDEA数据库工具重命名 ID 列，它的效果非常好！重命名表或列时，我不必担心外键。
See more details in IntelliJ IDEA Help | Renaming items.
在IntelliJ IDEA 帮助中查看更多详细信息| 重命名项目。
