更改mysql表中的主键列时出错

Question

我对SQL和MySQL非常陌生。我试图修改表中的主键列，使其自动递增。这个主键也是另一个表中的外键。由于与另一个表中的外键相关的错误，我无法修改此列。以下是错误：

mysql> desc favourite_food;
+-----------+----------------------+------+-----+---------+-------+
| Field     | Type                 | Null | Key | Default | Extra |
+-----------+----------------------+------+-----+---------+-------+
| person_id | smallint(5) unsigned | NO   | PRI | 0       |       |
| food      | varchar(20)          | NO   | PRI |         |       |
+-----------+----------------------+------+-----+---------+-------+
2 rows in set (0.09 sec)

mysql> alter table person modify person_id smallint unsigned auto_increment;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    22
Current database: bank

ERROR 1833 (HY000): Cannot change column 'person_id': used in a foreign key cons
traint 'fk_fav_food_person_id' of table 'bank.favourite_food'
mysql>

我确信这很简单，但我不知道为什么，我关注的书也没有说明原因。谢谢。

Answer 1

您不能更改主键，因为它在其他表中被引用为外键。这是因为引用完整性约束。

引用完整性尽管外键约束的主要目的是控制可以存储在外键表中的数据，但它也控制对主键表中数据的更改。该约束通过保证在主键表中的数据发生更改时，如果这些更改使到外键表中数据的链接无效，则不能对这些数据进行更改来强制引用完整性。如果试图删除主键表中的行或更改主键值，则当删除或更改的主键值与另一个表的外键约束中的值相对应时，该操作将失败。要成功更改或删除外键约束中的行，必须首先删除外键表中的外键数据或更改外键表（将外键链接到不同的主键数据）中的外密钥数据。

级联引用完整性

通过使用级联参照完整性约束，可以定义当用户尝试删除或更新现有外键指向的键时数据库引擎执行的操作。可以定义以下级联操作。

没有行动

数据库引擎引发错误，并且回滚对父表中的行的删除或更新操作。

串联

当父表中的行被更新或删除时，引用表中相应的行也被更新或删除。如果时间戳列是外键或被引用键的一部分，则不能指定CASCADE。不能为具有INSTEAD OF DELETE触发器的表指定ON DELETE CASCADE。不能为具有INSTEAD OF UPDATE触发器的表指定ON UPDATE CASCADE。

设置空

更新或删除父表中的相应行时，组成外键的所有值都设置为 NULL。要执行此约束，外键列必须可为空。不能为具有“代替更新”触发器的表指定。

设置默认值

如果父表中的相应行被更新或删除，组成外键的所有值都被设置为默认值。要执行此约束，所有外键列都必须有default定义。如果列可以为空，并且没有设置显式默认值，则NULL将成为该列的隐式默认值。不能为具有INSTEAD OF UPDATE触发器的表指定。

CASCADE、SET NULL、SET DEFAULT和NO ACTION不能在彼此具有引用关系的表上组合。如果数据库引擎没有遇到任何操作，它将停止并回滚相关的级联操作，设置NULL并设置默认操作。当DELETE语句导致CASCADE、SET NULL、SET DEFAULT和NO ACTION操作的组合时，所有CASCADE、SET NULL和SET DEFAULT操作都会在数据库引擎检查NO操作之前应用。

Answer 2

再次执行您的SQL，然后运行

show engine innodb status

在MySQL命令提示符下键入上述命令。它应该可以帮助您了解有关SQL执行失败的更多信息。

否则请尝试以下方法:

显示InnoDB状态

请看这里：错误代码1005，SQL状态HY000：无法创建表errno： 150

Answer 3

像这样做

--Drop fk
ALTER TABLE favourite_food DROP FOREIGN KEY fk_fav_food_person_id;
--Alter your pk
ALTER TABLE person modify person_id smallint unsigned auto_increment;
--Recreate fk
ALTER TABLE favourite_food ADD CONSTRAINT fk_fav_food_person_id FOREIGN KEY (person_id) REFERENCES person (person_id) ON DELETE CASCADE;

没有完全检查语法，但应该很接近