ERROR 1833 (HY000): Cannot change column MySQL

You can turn off foreign key checks:

SET FOREIGN_KEY_CHECKS = 0;

/* DO WHAT YOU NEED HERE */

SET FOREIGN_KEY_CHECKS = 1;

and with AUTO_INCREMENT you might face issue and for that you can use:

ERROR 1062: ALTER TABLE causes auto_increment resequencing, resulting in duplicate entry '1' for key 'PRIMARY'

get the current value of sql_mode by this query:

SELECT @@sql_mode;

Then set sql_mode to NO_AUTO_VALUE_ON_ZERO using below query:

SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';

Make sure to take backup before applying changes.


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;

Tags:

Mysql

Sql