MySQL 无法更改外键约束中使用的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13606469/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cannot change column used in a foreign key constraint
提问by theJava
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'
Here is my CREATE TABLE STATEMENT Which ran successfully.
这是我成功运行的 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)
);
Then i tried to execute this statement and i got the above error.
然后我尝试执行此语句，但出现上述错误。
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
回答by Dementic
You can turn off foreign key checks:
您可以关闭外键检查：
SET FOREIGN_KEY_CHECKS = 0;
/* DO WHAT YOU NEED HERE */
SET FOREIGN_KEY_CHECKS = 1;
Please make sure to NOT use this on production and have a backup.
请确保不要在生产中使用它并有备份。
回答by Michel Feldheim
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.
外键字段和引用的类型和定义必须相同。这意味着您的外键不允许更改您的字段类型。
One solution would be this:
一种解决方案是这样的：
LOCK TABLES
favorite_food WRITE,
person WRITE;
ALTER TABLE favorite_food
DROP FOREIGN KEY fk_fav_food_person_id,
MODIFY person_id SMALLINT UNSIGNED;
Now you can change you person_id
现在你可以改变你的person_id
ALTER TABLE person MODIFY person_id SMALLINT UNSIGNED AUTO_INCREMENT;
recreate foreign key
重新创建外键
ALTER TABLE favorite_food
ADD CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id)
REFERENCES person (person_id);
UNLOCK TABLES;
EDIT:Added locks above, thanks to comments
编辑：在上面添加了锁，感谢评论
You have to disallow writing to the database while you do this, otherwise you risk data integrity problems.
在执行此操作时，您必须禁止写入数据库，否则可能会出现数据完整性问题。
I've added a write lock above
我在上面添加了一个写锁
All writing queries in any other session than your own ( INSERT, UPDATE, DELETE) will wait till timeout or UNLOCK TABLES; is executed
除了您自己的 ( INSERT, UPDATE, DELETE)之外的任何其他会话中的所有写入查询都将等到超时或UNLOCK TABLES；被执行
http://dev.mysql.com/doc/refman/5.5/en/lock-tables.html
http://dev.mysql.com/doc/refman/5.5/en/lock-tables.html
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."
编辑 2：OP 要求对“外键字段和引用的类型和定义必须相同。这意味着您的外键不允许更改您的字段的类型”这一行进行更详细的解释。
From MySQL 5.5 Reference Manual: FOREIGN KEY Constraints
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.
外键和引用键中的对应列必须在 InnoDB 内部具有相似的内部数据类型，以便它们可以在不进行类型转换的情况下进行比较。整数类型的大小和符号必须相同。字符串类型的长度不必相同。对于非二进制（字符）字符串列，字符集和排序规则必须相同。
回答by RonaldBarzell
When you set keys (primary or foreign) you are setting constraints on how they can be used, which in turn limits what you can do with them. If you really want to alter the column, you could re-create the table without the constraints, although I'd recommend against it. Generally speaking, if you have a situation in which you want to do something, but it is blocked by a constraint, it's best resolved by changing what you want to do rather than the constraint.
当您设置键（主键或外键）时，您正在对它们的使用方式设置限制，这反过来又限制了您可以使用它们执行的操作。如果你真的想改变列，你可以在没有约束的情况下重新创建表，尽管我建议不要这样做。一般来说，如果你有一种情况，你想做某事，但被约束阻止了，最好通过改变你想做的事情而不是约束来解决。
