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

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