在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>

我相信这是一个简单的问题，但我不知道为什么，我所遵循的书籍也没有说明原因。谢谢。

- fdama

---

为什么不直接删除外键，执行修改语句，然后重新创建外键呢？ - Java Devil

你能给我演示一下怎么做吗？其次，为什么需要删除外键？ - fdama

Answer 1

做类似这样的事情

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

我还没有仔细检查语法，但应该很接近

- Java Devil

---

这正是你需要做的。错误告诉了你出了什么问题：你有一个外键约束，阻止你修改该列。移除该约束，进行更改，然后再添加约束即可。 - Volte

在执行此操作之前，请锁定表，以避免数据完整性问题。 - jairhumberto

Answer 2

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

show engine innodb status

将上述命令输入到MySQL命令提示符中。它应该会为您提供更多关于SQL无法执行的原因的信息。

否则，请尝试以下方法：

show innodb status

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

- sethu

Answer 3

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

参照完整性 虽然外键约束的主要目的是控制可以存储在外键表中的数据，但它也控制对主键表中数据的更改。该约束通过保证如果更改使得与外键表中的数据的链接无效，则不能对主键表中的数据进行更改来强制执行参照完整性。如果尝试删除主键表中的行或更改主键值，则当已删除或更改的主键值对应于另一个表的外键约束中的值时，操作将失败。要成功更改或删除外键约束中的行，必须首先删除外键表中的外键数据或更改外键表中的外键数据，以将外键链接到不同的主键数据。 级联参照完整性 通过使用级联参照完整性约束，您可以定义用户尝试删除或更新现有外键指向的键时数据库引擎采取的操作。可以定义以下级联操作。 NO ACTION 数据库引擎会引发错误，并回滚父表中行的删除或更新操作。 CASCADE 当在父表中更新或删除该行时，相应的引用表中的行也会被更新或删除。如果时间戳列是外键或引用键的一部分，则不能指定CASCADE。不能为具有INSTEAD OF DELETE触发器的表指定ON DELETE CASCADE。不能为具有INSTEAD OF UPDATE触发器的表指定ON UPDATE CASCADE。 SET NULL 当在父表中更新或删除相应行时，组成外键的所有值都设置为NULL。为了执行此约束，外键列必须可为空。不能为具有INSTEAD OF UPDATE触发器的表指定。 SET DEFAULT 当在父表中更新或删除相应行时，组成外键的所有值都设置为它们的默认值。为了执行此约束，所有外键列都必须具有默认定义。如果列可为空，并且没有显式的默认值设置，则NULL成为该列的隐式默认值。不能为具有INSTEAD OF UPDATE触发器的表指定。

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

- Juned Ahsan