CODEWITHSUNDEEP
mysqlsqldatabaseforeign-keysforeign-key-relationship
simd
simd

Reputation: 2019

Can't add AUTO_INCREMENT on existing column because of foreign key

I have primary key column, which has some external foreign key references. Very usual. I forgot to add AUTO_INCREMENT for it. Now I execute

ALTER TABLE chat.users CHANGE COLUMN user_id user_id INT(11) NOT NULL AUTO_INCREMENT ;

(PRIMARY KEY was defined separately)

it tells something about fk ERROR 1833: Cannot change column 'user_id': used in a foreign key constraint 'fk_chats_users' of table 'chat.chats'

I can't figure out why fk bother something about it's reference AUTO_INCREMENT.

Upvotes: 19

Views: 24903

Answers (2)

Leandro Carracedo
Leandro Carracedo

Reputation: 7345

The reason the FK bothers about your changes is because you are trying to alter it and is used in a constraint, remember that you are able to alter the data type.

So if you want to make the change to the FK, check this answer (remember to lock the tables before if you are making the change in a production environment).

Upvotes: 11

Rahul Tripathi
Rahul Tripathi

Reputation: 172378

(PRIMARY KEY was defined separately)

If you have defined primary key on your column then I dont think there is any need to modify your column and add auto_increment to it. Primary keys are auto incremented by default.

However if you want to set the auto_increment feature on it then try like this:

--Drop foreign key
ALTER TABLE chat.users DROP FOREIGN KEY fk_chats_users;
--Alter your primary key
ALTER TABLE person modify user_id INT(11) not null auto_increment;
--Recreate foreign key
ALTER TABLE chat.users ADD CONSTRAINT fk_chats_users FOREIGN KEY (user_id) REFERENCES chat.users(user_id) ON DELETE CASCADE;

Upvotes: -4

Related Questions