无法更改外键约束中使用的列 [英] Cannot change column used in a foreign key constraint

问题描述

当我尝试更改表时出现此错误.

I got this error when i was trying to alter my table.

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.

Here is my CREATE TABLE STATEMENT Which ran successfully.

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

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

Then i tried to execute this statement and i got the above error.

ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;

推荐答案

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

The type and definition of foreign key field and reference must be equal. This means your foreign key disallows changing the type of your field.

一个解决方案是这样:

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

Now you can change you 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;

感谢评论，在上方添加了锁

Added locks above, thanks to comments

执行此操作时，您必须禁止写入数据库， 否则，您将面临数据完整性问题的风险.

You have to disallow writing to the database while you do this, otherwise you risk data integrity problems.

我在上面添加了写锁

除您自己的会话(INSERT, UPDATE, DELETE)以外的任何其他会话中的所有写查询都将等待，直到超时或UNLOCK TABLES；被执行

All writing queries in any other session than your own ( INSERT, UPDATE, DELETE ) will wait till timeout or UNLOCK TABLES; is executed

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

OP要求对行外键字段和引用的类型和定义必须相同.这意味着您的外键不允许更改字段的类型."

EDIT 2: OP asked for a more detailed explanation of the line "The type and definition of foreign key field and reference must be equal. This means your foreign key disallows changing the type of your field."

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

外键和引用键中的对应列必须 在InnoDB内部具有相似的内部数据类型，因此它们可以 没有类型转换的情况下进行比较.整数类型的大小和符号 必须相同.字符串类型的长度不必相同.为了 非二进制(字符)字符串列，字符集和排序规则 必须相同.

Corresponding columns in the foreign key and the referenced key must have similar internal data types inside InnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

这篇关于无法更改外键约束中使用的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！