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;