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

问题描述

我是SQL和MySQL的新手.我正在尝试修改表中的主键列，以使其自动递增.该主键还是另一个表中的外键.由于与另一个表中的外键有关的错误，我无法修改此列.这是错误:

I am very new to SQL and MySQL. I am trying to modify a primary key column in a table so that it auto-increments. This primary key is also a foreign key in another table. I am not able to modify this column due to an error related to the foreign key in the other table. Here is the error:

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>

我敢肯定这很简单，但是我不知道为什么，而我遵循的这本书并没有说明原因.谢谢.

I'm sure it is something simple, but I can't figure out why and teh book I am following does not indicate why. Thanks.

推荐答案

执行类似操作

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

尚未完全检查语法，但应将其关闭

Haven't checked syntax exactly, but should be close

这篇关于更改mysql表中的主键列时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！