CODEWITHSUNDEEP
mysql
SOURAV SANTRA
SOURAV SANTRA

Reputation: 171

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

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.

Upvotes: 17

Views: 29968

Answers (3)

Reinaldo Zamboni
Reinaldo Zamboni

Reputation: 1

SET FOREIGN_KEY_CHECKS = 0;

alter table `user_details` drop foreign key `nmFK`;

alter table `user_type` drop primary key, change `user_type_id` `user_type_id` [data type] not null, add primary key (`user_type_id`);

alter table `user_details` change `user_type_id` `user_type_id` [data type] not null;

alter table `user_details` add constraint `nmFK` foreign key (`user_type_id`) references `user_type`(`user_type_id`);

SET FOREIGN_KEY_CHECKS = 1;

Upvotes: 0

Adarsh Madrecha
Adarsh Madrecha

Reputation: 7886

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;

Upvotes: 7

Ciprian Stoica
Ciprian Stoica

Reputation: 2439

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;
  1. 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.

  1. Finally recreate the foreign key:

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

Upvotes: 17

Related Questions