4

I'm trying to remove Auto_Increment from the column _id in my MySQL database. However that column is the primary key for the table and when I'm using this command

ALTER TABLE Profile
MODIFY _id INT PRIMARY KEY NOT NULL

I get an error telling me that I can't do that since there are other tables which references the primary key.

My question is therefore: Is there a way to get around this problem?

Michael Tot Korsgaard
  • 3,892
  • 11
  • 53
  • 89
  • 1
    Remove the foreign key(s) from other table, alter your table and recreate the forreign key(s). – Jens Jan 11 '15 at 17:44
  • 5
    `SET FOREIGN_KEY_CHECKS=0;` enable it when you`re done But primary key and auto increment are almost synonyms why do you want to do that? – Mihai Jan 11 '15 at 17:50
  • @Mihai: It is as a possible solution to this problem I have http://stackoverflow.com/questions/27887585/insert-a-specific-id-into-auto-incremented-field-in-mysql-with-entity-framework – Michael Tot Korsgaard Jan 11 '15 at 17:56
  • Does this answer your question? [How can I temporarily disable a foreign key constraint in MySQL?](https://stackoverflow.com/questions/15501673/how-can-i-temporarily-disable-a-foreign-key-constraint-in-mysql) – Channa May 01 '21 at 17:17

2 Answers2

12

the easiest and fastest way is the following:

set foreign_key_checks = 0;
alter table Profile change column _id _id INT NOT NULL;
set foreign_key_checks = 1;

found here

Pontios
  • 2,377
  • 27
  • 32
1

Thre options:

1.Delete relationship before making this change.

2.Delete other table/s before making this change.

3.Alter relationship(tables) with somthing like on update / cascade (not sure if this will help)

Mark
  • 684
  • 2
  • 9
  • 25