MySQL数据库——由于外键的存在导致修改存在表引发的一个错误

1. 错误的来源

首先，创建了person表：

mysql> CREATE TABLE person
    -> (person_id SMALLINT UNSIGNED,
    -> fname VARCHAR(20),
    -> lname VARCHAR(20),
    -> gender ENUM('M', 'F'),
    -> birth_date DATE,
    -> street VARCHAR(30),
    -> city VARCHAR(20),
    -> state VARCHAR(20),
    -> country VARCHAR(20),
    -> postal_code VARCHAR(20),
    -> CONSTRAINT pk_person PRIMARY KEY (person_id)
    -> );

其次，创建了表favorite_food：

mysql> CREATE TABLE favorite_food
    -> (person_id SMALLINT UNSIGNED,
    -> food VARCHAR(20),
    -> CONSTRAINT pk_favorite_food PRIMARY KEY (person_id, food),
    -> CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id)
    ->  REFERENCES person (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 'bank.favorite_food'

在上述创建的个人信息表person中，我们加了主键的约束。

"person"表中"person_id"列是"favorite_food"表中"persoon_id"列的 PRIMARY KEY（主键）

"favorite_food"表中"person_id"列是"person"表中"persoon_id"列的 FOREIGN KEY（外键）

2. 一种解决方案

由于外键的影响导致我们无法随便修改表结构。一种解决方法是：先撤销外键约束，修改相应的表属性，在进行外键约束。

撤销外键约束：

mysql> LOCK TABLE favorite_food WRITE, person WRITE;

mysql> ALTER TABLE favorite_food DROP FOREIGN KEY fk_fav_food_person_id, MODIFY person_id SMALLINT UNSIGNED;

xg修改相应表属性：

mysql> ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

再次添加外键约束：

mysql> ALTER TABLE favorite_food ADD CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id) REFERENCES person (person_id);

mysql> UNLOCK TABLES;