Menu
We're trying to rename a column in MySQL (5.1.31, InnoDB) that is a foreign key to another table.
At first, we tried to use Django-South, but came up against a known issue:
http://south.aeracode.org/ticket/243
OperationalError: (1025, "Error on rename of './xxx/#sql-bf_4d' to './xxx/cave_event' (errno: 150)")
AND
Error on rename of './xxx/#sql-bf_4b' to './xxx/cave_event' (errno: 150)
This error 150 definitely pertains to foreign key constraints. See e.g.
What does mysql error 1025 (HY000): Error on rename of './foo' (errorno: 150) mean?
http://www.xaprb.com/blog/2006/08/22/mysqls-error-1025-explained/
So, now we're trying to do the renaming in raw SQL. It looks like we're going to have to drop the foreign key first, then do the rename, and then add the foreign key back again. Does that sound right? Is there a better way, since this seems pretty confusing and cumbersome?
Any help would be much appreciated!
208
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.
Rename MySQL Column with the CHANGE Statement Enter the following command in your MySQL client shell to change the name of the column and its definition: ALTER TABLE table_name CHANGE old_column_name new_col_name Data Type; You can change the data type of the column or keep the existing one.
A foreign key can also have different column names than the primary key. The foreign key and primary key can also have different default values. However, since values in the referenced table must be unique, default values are not much used and are rarely used for columns that are part of a primary key.
Here is how you would do that: ALTER TABLE my_table ADD FOREIGN KEY (key) REFERENCES other_table(id) ON DELETE SET NULL; And that's it!! That's how you change a foreign key constraint in MySQL!
AFAIK, dropping the constraint, then rename, then add the constraint back is the only way. Backup first!
94
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
