How do I rename a foreign key in mysql?

Question

We've just completed a long-running migration on a large table, and ended up with the following constraint on our conversation_tags table:

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

Unfortunately, there was a bug somewhere, because what we wanted was:

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

Dropping and re-adding the constraint would mean another two long queries. Is there any way to rename the constraint in a single query?

Answer 1

The feature does not seems to be available in mysql ALTER TABLE syntax.

However it is supported for Oracle.

Answer 2

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

Answer 3

I'm sorry, but constraints can only be dropped and re-attacched in mySQL

Answer 4

From the documentation:

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.

This way you can combine the drop and recreate into one query, and that should be faster than dropping the constraint and creating it in two queries:

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