Rename column name with foreign key constraint in MySql query

Rename column name with foreign key constraint in MySql query

If we have already defined foreign key constraint on a column in mysql, then after some if we want to change the column name then it can be a little bit difficult. So here i will tell you how can we rename column name with foreign key constraint in MySql query.

Suppose you have a mysql database table with some columns. And we want to change the column name later. it can be done easily with rename query. Suppose if it has been already defined a foreign key on that column, then when we run simple column name  rename query. But it may lead an error in mysql query and you would get an error like below.

Query error:

#1025 - Error on rename of './learn/#sql-46c_246' to './learn/my_table' (errno: 150)

The idea is to rename the column having foreign key constraint is first we can drop the foreign key and then change the column name and after that  we can add the same foreign key constraint back in that particular column.

Let’s see this using an example, suppose we have a table named my_table with a foreign key constraint. if not, then it can be created easily using the following query command.

CREATE TABLE my_table (
  id int unsigned not null AUTO_INCREMENT key,
  name VARCHAR(255) default null,
        user_id int unsigned not null,
        CONSTRAINT `my_table_user_id_fk`
        FOREIGN KEY (user_id) REFERENCES users (id)
          ON DELETE CASCADE
          ON UPDATE CASCADE
);

You can see that in the above query we user_id column on which foreign key is defined. So for in this case we will rename the column name user_id to sender_id. So let’s have a look in the query below.

ALTER TABLE `my_table`
  DROP FOREIGN KEY `my_table_user_id_fk`,
  CHANGE COLUMN user_id sender_id int unsigned not null,
  ADD CONSTRAINT `my_table_sender_id_fk` 
  FOREIGN KEY (`sender_id`) REFERENCES `users` (`id`)
  ON DELETE CASCADE
        ON UPDATE CASCADE

So here in the above query we have dropped foreign key then again we added that key to the rename column name that is sender_id.