Menu
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?
460
You can use the sp_rename system stored procedure to rename a foreign key constraint in SQL Server. The purpose of this stored procedure is to allow you to rename user-created objects in the current database, so you can also rename other objects such as tables, columns, alias data types, etc.
But i found the solution of mysql rename foreign key constraint using mysql query, First we have to drop the foreign key, then change the column, at last we need to again add the foreign key constraint back in column.
You can drop a foreign key constraint using the following ALTER TABLE syntax: ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol; If the FOREIGN KEY clause defined a CONSTRAINT name when you created the constraint, you can refer to that name to drop the foreign key constraint.
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`); If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
