重命名MySQL中的外键列

6 回答

• 3

  AFAIK，删除约束，然后重命名，然后添加约束是唯一的方法 . 先备份！

  回复于 2024-04-24T18:47:43+08:00
• 0

  如果有人在寻找语法，它会像这样：

  alter table customer_account drop foreign key `FK3FEDF2CC1CD51BAF`; 
  
  alter table customer_account  add constraint `FK3FEDF2CCD115CB1A` foreign key (campaign_id) REFERENCES campaign(id);
  

  回复于 2024-04-24T18:47:43+08:00
• 0

  这是常规键的SQL语法

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

  回复于 2024-04-24T18:47:43+08:00
• 0

  扩展@Dewey 's answer, here' s一个小脚本，以有用的方式重命名由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_%';
  

  一点输出：

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

  回复于 2024-04-24T18:47:43+08:00
• 25

  以下查询将自动构建正确的语法 . 只需执行返回的每一行，所有的FKEY都将消失 .

  我将反向（加回来）作为练习给你 .

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

  回复于 2024-04-24T18:47:43+08:00
• 48

  如果使用GUI工具，此任务会变得更简单 . 我尝试使用IntelliJ IDEA Database tool重命名ID列，它就像一个魅力！重命名表或列时，我不必担心外键 .

  在IntelliJ IDEA Help | Renaming items中查看更多详细信息 .

  回复于 2024-04-24T18:47:43+08:00