How to Resolve ERROR 1025 (HY000): Error on rename of MySQL

  • by

ERROR 1025 (HY000)

An error ERROR 1025 (HY000) was thrown when I tried to rename a column in MySQL.

mysql> alter table sales change column sales_orders order_id int(11) not null default 1;
ERROR 1025 (HY000): Error on rename of './testdb/#sql-xxxxx' to './testdb/sales' (errno: 150)

Let's check the contents of all the error codes in the stack.

mysql> ! perror 1025
MySQL error code 1025 (ER_ERROR_ON_RENAME): Error on rename of '%-.210s' to '%-.210s' (errno: %d)
mysql> ! perror 150
MySQL error code 150: Foreign key constraint is incorrectly formed

It tells us that there is a foreign key constraint on this column to prevent you from renaming it.

Solution

Let's check the constraint name before dropping it.

mysql> show create table sales;
...
  CONSTRAINT `sales_orders_fk` FOREIGN KEY (`sales_orders`) REFERENCES `orders` (`order_id`),
...

Let's drop the foreign key constraint.

mysql> alter table sales drop foreign key sales_orders_fk;
Query OK, 1289 rows affected (0.6 sec)
Records: 1289 Duplicates: 0 Warnings: 0

Now, we can try to rename the column again.

mysql> alter table sales change column sales_orders order_id int(11) not null default 1;
Query OK, 1289 rows affected (0.4 sec)
Records: 1289  Duplicates: 0  Warnings: 0

It's successful now. No more ERROR 1025 (HY000)

Further reading: How to Resolve ERROR 1215 (HY000): Cannot add foreign key constraint in MySQL.

Leave a Reply

Your email address will not be published. Required fields are marked *