Can't add AUTO_INCREMENT on existing column because of foreign key
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).
(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 keyALTER TABLE chat.users DROP FOREIGN KEY fk_chats_users;--Alter your primary keyALTER TABLE person modify user_id INT(11) not null auto_increment;--Recreate foreign keyALTER TABLE chat.users ADD CONSTRAINT fk_chats_users FOREIGN KEY (user_id) REFERENCES chat.users(user_id) ON DELETE CASCADE;