无法更改外键约束中使用的列

Question

尝试更改表时出现此错误。

Error Code: 1833. Cannot change column 'person_id': used in a foreign key constraint 'fk_fav_food_person_id' of table 'table.favorite_food'

这是我成功运行的CREATE TABLE STATEMENT。

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)
);

然后，我尝试执行此语句，但出现了以上错误。

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

Answer 1

外键字段和引用的类型和定义必须相同。这意味着您的外键不允许更改字段的类型。

一种解决方案是：

LOCK TABLES 
    favorite_food WRITE,
    person WRITE;

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

现在您可以更改您的person_id

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

重新创建外键

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

UNLOCK TABLES;

编辑： 上面添加了锁，感谢评论

在执行此操作时，您必须禁止写入数据库，否则可能会冒数据完整性问题的风险。

我在上面添加了写锁

除您（INSERT, UPDATE, DELETE）以外的任何其他会话中的所有写作查询将等待超时或UNLOCK TABLES; 被执行

http://dev.mysql.com/doc/refman/5.5/zh-CN/lock-
tables.html

编辑2：OP要求对行进行更详细的说明“外键字段和引用的类型和定义必须相等。这意味着您的外键不允许更改字段的类型。”

从MySQL 5.5参考手册：FOREIGN KEY约束

外键和引用键中的对应列在InnoDB中必须具有相似的内部数据类型，以便可以在不进行类型转换的情况下进行比较。整数类型的大小和符号必须相同。字符串类型的长度不必相同。对于非二进制（字符）字符串列，字符集和排序规则必须相同。