CODEWITHSUNDEEP
mysql
user3391137
user3391137

Reputation: 441

add auto increment(AUTO_INCREMENT) for already foreign key assigned attribute?

user table is defined without AUTO_INCREMENT on user_id. I want to change it to AUTO_INCREMENT field. But trouble is user_id is referenced by many tables in foreign key constraint.

Can we solve it without deleting all foreign keys?

Upvotes: 3

Views: 7635

Answers (1)

Michał Szkudlarek
Michał Szkudlarek

Reputation: 1502

You can do it without deleting, using foreign_key_checks:

set foreign_key_checks = 0;
--before alter, set AUTO_INCREMENT to a max existing value + 1 (see below)
alter table users modify column user_id INT NOT NULL AUTO_INCREMENT;
set foreign_key_checks = 1;

DEMO HERE

You can also do it with deleting, example below.

Assuming that users_ref is a reference table.

ALTER TABLE users_ref DROP FOREIGN KEY fk_users;

Set the AUTO_INCREMENT start to a max existing value + 1, ie:

select max(user_id) + 1 from users; --let's say it returns 5
ALTER TABLE users AUTO_INCREMENT=5;

Add AUTO_INCREMENT:

alter table users modify column user_id INT NOT NULL AUTO_INCREMENT;

Recreate constraint:

ALTER TABLE users_ref add CONSTRAINT fk_users FOREIGN KEY (user_id) REFERENCES users(user_id);

Demo can be found HERE.

Upvotes: 13

Related Questions