ERROR 1833 (HY000): Cannot change column MySQL

Question

I'm trying to add auto increment to the primary key person_id of a person table. However when I run the command I get the following error.

ERROR 1833 (HY000): Cannot change column 'person_id': used in a foreign key constraint 'fk_fav_food_person_id' of table 'bank.favorite_food'

for the following command.I'm using MySQL 5.7.20

mysql> ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

Answer 1

Because foreign keys must match the data type of the column they reference, it makes sense that changing the data type of a column is likely to upset the foreign key that references it.

It seems that MySQL has a safeguard against this, and denies an attempt to MODIFY the column. But it's not smart enough to check whether the specific modification you're making will in fact change the data type. It just denies any attempt to modify that column.

You can work around this by temporarily disabling foreign key checks. I recreated your table and confirmed it:

mysql> ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
ERROR 1833 (HY000): Cannot change column 'person_id': used in a foreign key constraint 'favorite_food_ibfk_1' of table 'test.favorite_food'

mysql> set foreign_key_checks=0;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> set foreign_key_checks=1;