CODEWITHSUNDEEP
mysql
JLCDev
JLCDev

Reputation: 629

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.

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

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

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.

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:

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

Any tips?

Thanks

Upvotes: 17

Views: 25709

Answers (1)

Paul Spiegel
Paul Spiegel

Reputation: 31772

You need to disable foreign key checks:

SET FOREIGN_KEY_CHECKS=0;

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

SET FOREIGN_KEY_CHECKS=1;

Upvotes: 34

Related Questions