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

Question

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.

Pinite

Possible duplicate of Cannot change column used in a foreign key constraint – Daly

Answer 1

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).

Sanitary

So FK just block any altering even if it leave data type and all data the same? I did't thought it's so rude. Used "SET FOREIGN_KEY_CHECKS = 0;". Very helpful in my case. Thanx! – Pinite

Exactly, that's the way to make sure you don't violate referential integrity. You're welcome! – Sanitary

"SET FOREIGN_KEY_CHECKS=0" Saved my day. Thanks. – Technic

@user3537411 Yup. I can agree with the ban if we are changing data type or data length, but adding and removing AUTO_INCREMENT shouldn't ruin the connection with other tables. It's kind of weird why we are disallowed to. – Easel

Answer 2

(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;

Achondroplasia

Primary keys are auto incremented by default? I can't see it. If I have't AUTO_INCREMENT explicity, then after "insert into users (username) values("me")" where I expect user_id to be generated incrementally, it tells me "Error Code: 1364. Field 'user_id' doesn't have a default value " . – Pinite

And if I add 0 as default value explicity, it will tell me "Error Code: 1062. Duplicate entry '0' for key 'PRIMARY' " after second try. – Pinite

SHOW CREATE TABLE for each relevant table. Without that, we are guessing. – Kunz