CODEWITHSUNDEEP
mysqlsql
Steven Aguilar
Steven Aguilar

Reputation: 3049

ERROR 1833 (HY000): Cannot change column MySQL

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;

enter image description here

Upvotes: 14

Views: 25018

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562621

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.

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'

That fails as expected.

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

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;

Upvotes: 39

Nirav Mehta
Nirav Mehta

Reputation: 7073

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.

Upvotes: 3

Related Questions