mysql修改外键字段报异常,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;