How to change data type of a foreign key when its a primary key in another table in mysql

Question

I have a table user_type and user_type_id is the primary key of that table and I have another table user_details where user_type_id is foreign key.

I want to change data type of user_type_id column.

Answer 1

I had a similar scenario, the mobo column was of CHAR(10) datatype and wanted to convert it into VARCHAR(16) datatype.

I used below queries to for migrating the DB.

SET foreign_key_checks = 0;
ALTER TABLE `user`
CHANGE `mobno` `mobno` varchar(16) COLLATE 'utf8_general_ci' NOT NULL FIRST;
ALTER TABLE `staff`
CHANGE `mobno` `mobno` varchar(16) COLLATE 'utf8_general_ci' NOT NULL AFTER `officeid`;
SET foreign_key_checks = 1;    

The important note is to use SET foreign_key_checks = 0; before running the queries and then again setting it back to 1 by running SET foreign_key_checks = 1;

Answer 2

You must perform several steps.

On short: drop the foreign key, modify the fields user_type_id from varchar to int, re-create the foreign key.

It is very important to make back-ups before proceeding. That varchar to int conversion might mess-up the consistency if user_type_id values contain anything else that digits.

In detail:

1. First you need to drop the foreign key from user_details. To get the constraint name defining the foreign key run:

SHOW CREATE TABLE user_details;

You will see something like:

CONSTRAINT 'some_constraint_name' FOREIGN KEY ('user_type_id') REFERENCES 'user_type' ('user_type_id')

Delete that constraint by running

ALTER TABLE user_details DROP FOREIGN KEY some_constraint_name;

2. Now you can change user_type_id from varchar to int on both user_type and user_details tables:

ALTER TABLE user_type CHANGE user_type_id user_type_id INT(length);

ALTER TABLE user_details CHANGE user_type_id user_type_id INT(length);

Replace length with the old varchar field length.

3. Finally recreate the foreign key:

ALTER TABLE user_details ADD FOREIGN KEY (user_type_id) REFERENCES user_type(user_type_id);