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

我在修改桌子的时候出了这个错误。

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

下面是我的 CREATETABLE 语句，它成功地运行了。

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;
160490 次浏览
小开

当您设置键(主键或外键)时，您正在设置如何使用它们的约束，这反过来又限制了您可以对它们进行的操作。如果您真的想要更改列，那么您可以重新创建没有约束的表，尽管我建议不要这样做。一般来说，如果你有一个情况，你想做的事情，但它是由一个约束阻塞，它是最好的解决方案，改变你想做的事情，而不是约束。

小开
最佳答案

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

一种解决方案是:

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/en/lock-tables.html

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

来自 MySQL 5.5参考手册: FOREIGN KEY 约束

外键和引用键中的对应列必须 在 InnoDB 内部具有类似的内部数据类型，因此它们可以 整数类型的大小和符号 必须相同。字符串类型的长度不必相同。对于 非二进制(字符)字符串列、字符集和排序规则 肯定是一样的。

小开

您可以关闭外键检查:

SET FOREIGN_KEY_CHECKS = 0;


/* DO WHAT YOU NEED HERE */


SET FOREIGN_KEY_CHECKS = 1;

请确保不要在生产上使用这个，并有一个备份。

小开

转到有问题的表的结构选项卡。 在操作下有索引。 放下武器

一旦你完成了必要的修改, 恢复外键并恢复已删除的索引。然后确保您的结构是相同的，并没有改变

小开

在我的情况下，有必要添加 GLOBAL。

SET FOREIGN_KEY_CHECKS = 0;
SET GLOBAL FOREIGN_KEY_CHECKS=0;


/* DO WHAT YOU NEED HERE */


SET FOREIGN_KEY_CHECKS = 1;
SET GLOBAL FOREIGN_KEY_CHECKS=1;

提交答案