mysql, alter column remove primary key and auto incremement

Question

I'm changing my mysql db table from an id (auto) to a uid.

ALTER TABLE companies DROP PRIMARY KEY;
ALTER TABLE companies ADD PRIMARY KEY (`uuid`);

This is the error I get..

[SQL] ALTER TABLE companies DROP PRIMARY KEY;
[Err] 1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Which I understand, I need to change the id to a non-autoincrement because I drop it as the primary key.? What is the syntax to change a column to remove primary key and auto increment?

ALTER TABLE companies change id id ?????????? int(11)

score 53 · Accepted Answer · edited Feb 23 '15 at 07:39

53

If you need to remove the auto-increment and the primary key from the id column in a single SQL statement, this should do:

ALTER TABLE companies DROP PRIMARY KEY, CHANGE id id int(11);

In fact, you should be able to do everything in a single ALTER TABLE query:

ALTER TABLE companies
DROP PRIMARY KEY,
CHANGE id id int(11),
ADD PRIMARY KEY (uuid);

edited Feb 23 '15 at 07:39

Pacerier

86,231
106
366
634

answered Jun 22 '10 at 05:18

Lauri Lehtinen

10,647
2
31
29

If I do it as 3 statements it will work.. ALTER TABLE companies change id id int(11); ALTER TABLE companies DROP PRIMARY KEY; ALTER TABLE companies ADD PRIMARY KEY (uuid); – Brett Jun 22 '10 at 05:28
1

In my situation, I only want to drop the auto-increment. I tried this: `ALTER TABLE myTable CHANGE _id _id INT(12)` but it says "syntax error near CHANGE". What am I doing wrong here? – Nii Laryea Jun 07 '16 at 02:35

score 7 · Answer 2 · edited Feb 23 '15 at 13:50

7

When you're not changing the name of the column you can use MODIFY:

ALTER TABLE `companies` MODIFY `id` int(11), 
                           DROP PRIMARY KEY, 
                   ADD PRIMARY KEY (`uuid`);

By doing this all in one alter statement it's also treated as atomic, so there's no chance of inconsistency between queries (unlike running multiple statement in row).

edited Feb 23 '15 at 13:50

Pacerier

86,231
106
366
634

answered Mar 07 '14 at 15:03

Ray

40,256
21
101
138

1

Best way to do, other answers suggest using `CHANGE` which is unnecessary as column name remains same, `MODIFY` is the correct choice here. – Payel Senapati Sep 13 '21 at 12:54

score 5 · Answer 3 · edited Aug 24 '14 at 18:38

5

The query for remove auto increment is:

alter table companies DROP PRIMARY KEY,
change id id int(11) NOT NULL

Now you can see structure of table is without auto increment.

If you want to add primary key another column, then use this query

alter table companies add PRIMARY KEY(uuid)

If you want to drop auto increment,primary key and add primary key to new column in same query, then use this query

alter table comapnies DROP PRIMARY KEY,
   change id id int(11) NOT NULL,
   add PRIMARY KEY(uuid)

edited Aug 24 '14 at 18:38

Brad Larson

170,088
45
397
571

answered Feb 07 '14 at 05:29

John

723
1
10
12

1

This will change your primary key to the type INT(11) NOT NULL if it was not already that exact data type and size. – Brian Hannay Apr 08 '18 at 20:46

score 1 · Answer 4 · answered Oct 14 '21 at 18:11

This is the syntax to change a column to remove the primary key and auto_increment

ALTER TABLE table_name DROP PRIMARY KEY,
CHANGE old_key_name new_key_name new_column_datatype

In your case this is the answer:

ALTER TABLE companies DROP PRIMARY KEY, CHANGE ID UID;

If we want to drop a primary key, also we need to drop the auto_increment from that primary key, both at the same time using the next syntax:

ALTER TABLE table_name DROP PRIMARY KEY,
MODIFY primary_column_name datatype

there is no need to use the change command because change is used to change the name and datatype of a column so instead of change we can use modify to change only the datatype of a column like in the previous case.

score 0 · Answer 5 · answered Jun 29 '22 at 19:50

If you have a foreign key column, you need to delete the constraint first. This is how I did to remove the auto increment:

ALTER TABLE portal_user_privileges DROP FOREIGN KEY FK__privileges; 

alter table privileges DROP PRIMARY KEY;

alter table privileges add PRIMARY KEY(id);

ALTER TABLE portal_user_privileges ADD CONSTRAINT FK__privileges FOREIGN KEY (privileges_id) REFERENCES privileges(id)  ON UPDATE NO ACTION ON DELETE NO ACTION;

mysql, alter column remove primary key and auto incremement

5 Answers5

Linked