在看《SQL学习指南》的第二章的时候,它要求调整 person 表的结构,需要将 person_id 改为自增长的。但是 person_id 作为另一张表的外键,于是引发了一个错误:

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 'fk_fav_food_person_id' of table 'learning_sql.favorite_food'

然后我百度了一下解决方案,发现需要先删除另外一张表的外键约束,然后再改 person_id。如果有多张表都有这个外键约束,这样做不是很麻烦么?后来我在 StackOverFlow 找到了正确的解决方案:

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;

参考资料:

ERROR 1833 (HY000): Cannot change column MySQL

最后修改于 2019-04-02 16:48:17
上一篇