Search code examples
mysqlsqldatabaseforeign-keysforeign-key-relationship

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.

Solution

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