Question

我们刚刚在一个大表上完成了一个长时间的迁移，并在conversation_tags表上得到了以下约束：

CONSTRAINT `conversation_tags_ibfk_1` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`)

不幸的是，某个地方出现了一个错误，因为我们想要的是：

CONSTRAINT `fk_conversation_tags_tags` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`)

删除并重新添加约束将意味着另外两个长查询。是否有任何方法可以在单个查询中重命名约束？

Answer 1

从文档：

Multiple ADD, ALTER, DROP, and CHANGE clauses are permitted in a single ALTER TABLE statement, separated by commas. This is a MySQL extension to standard SQL, which permits only one of each clause per ALTER TABLE statement.

通过这种方式，您可以将删除和重新创建合并到一个查询中，这应该比删除约束并在两个查询中创建约束更快：

ALTER TABLE conversation_tags
DROP FOREIGN KEY `conversation_tags_ibfk_1`,
ADD CONSTRAINT `fk_conversation_tags_tags` FOREIGN KEY (`tag_id`) REFERENCES `tags` (`id`);

Answer 2

很抱歉，约束只能在mySQL中删除并重新附加

Answer 3

该功能在mysql ALTER TABLE语法中似乎不可用。

但是，Oracle支持它。

Answer 4

Please refer to my answer in MySQL terminology "constraints" vs "foreign keys" difference? to understand why the constraint name was different than what you desired. However, MySQL doesnot have rename constraint feature and hence need to DROP and ADD FK with desired name . https://dev.mysql.com/doc/refman/5.5/en/alter-table.html

友情链接