如何从 mysql 表中删除 auto_increment [英] How to drop auto_increment from a mysql table

问题描述

这应该是一个非常简单的问题，但我找不到有效的解决方案.

this should be a very easy issue but I couldn't find a solution that works.

我将日期从 Oracle 迁移到 MYSQL，在此过程中，所有主键都设置为 auto_increment.

I migrate the date from Oracle to MYSQL and during the process, all primary keys were set to auto_increment.

但是，有很多确定的关系(父PK与子PK相同).

However, there are a lot of identified relationships (parent PK is the same of children).

所以正确的交易方式是插入到父表中，从这个交互中获取result.insertId，然后在子表中插入相同的值.我知道我可以简单地忽略在插入命令中发送 id 的 auto_increment，但我不想就这样放手.

So the correct way to do the transaction is to insert into the parent tables, get result.insertId from this interaction and then insert the same value in the child table. I know that I could simply ignore the auto_increment sending the id in the insert command but I didn't like to just let this go.

因为我读到的解决方案说我需要将列更改为完全相同的规范但 auto_increment，我运行以下 SQL:

As the solutions I read about say that I need to change the column to the exactly the same specification but auto_increment, I run the following SQL:

alter table added_object modify column id_interaction_object int(11) not null;

.. 我收到以下消息:

.. And I get the following message:

错误 1833 (HY000):无法更改列id_interaction_object":已使用在表的外键约束FK__METRIC__ADDED_OBJECT"中'公制'

ERROR 1833 (HY000): Cannot change column 'id_interaction_object': used in a foreign key constraint 'FK__METRIC__ADDED_OBJECT' of table 'metric'

有什么建议吗?

谢谢

推荐答案

您需要禁用外键检查:

SET FOREIGN_KEY_CHECKS=0;

alter table added_object modify column id_interaction_object int(11) not null;

SET FOREIGN_KEY_CHECKS=1;

这篇关于如何从 mysql 表中删除 auto_increment的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！