Add Auto-Increment ID to existing table?

Question

I have a pre-existing table, containing 'fname', 'lname', 'email', 'password' and 'ip'. But now I want an auto-increment column. However, when I enter:

ALTER TABLE users
ADD id int NOT NULL AUTO_INCREMENT

I get the following:

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Any advice?:)

tried that, but it just returns the table. what do you need? — Charles Jenkins, Feb 07 '13 at 14:46
MODIFY can also be used as : ALTER TABLE users MODIFY id int NOT NULL AUTO_INCREMENT ; /* AUG Dated 2019*/ — Vilas Joshi, Aug 09 '19 at 04:11

score 149 · Answer 1 · edited Nov 21 '20 at 17:02

149

Try this

ALTER TABLE `users` ADD `id` INT NOT NULL AUTO_INCREMENT;

for an existing primary key

edited Nov 21 '20 at 17:02

Biraag S Prabhakar

3
2

answered Feb 07 '13 at 14:31

Muhammad Asif Mahmood

1,650
1
9
10

12

#1068 - Multiple primary key defined – Charles Jenkins Feb 07 '13 at 14:35
7

In SQL Server 2008, replacing 'AUTO_INCREMENT' with 'IDENTITY(1,1)' worked for me. – Greg A Aug 04 '15 at 13:35
14

Note that this adds an ID-column as the last column in the table. Add `FIRST` to the query to make it the first in this table. – Tobias Baumeister Oct 07 '16 at 11:32
9

@CharlesJenkins To avoid the "Multiple primary key" error, drop the existing primary key before defining the new one: `ALTER TABLE users DROP PRIMARY KEY, ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY` – Sean the Bean Sep 27 '17 at 19:52
9

To make it the first column, use `FIRST` e.g `ALTER TABLE tbl1 ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST` – Tahir Raza Jul 04 '18 at 14:43
Got errors with that sintax this worked for me ALTER TABLE `users` ADD `id` INT NOT NULL AUTO_INCREMENT , ADD PRIMARY KEY (`id`); – lisandro Nov 27 '19 at 06:24

score 111 · Answer 2 · answered Jun 27 '13 at 15:00

111

If you don't care whether the auto-id is used as PRIMARY KEY, you can just do

ALTER TABLE `myTable` ADD COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST;

I just did this and it worked a treat.

answered Jun 27 '13 at 15:00

Coderer

25,844
28
99
154

1

This worked for me and set the auto_increment id column as the primary key. – reflexiv Jul 04 '13 at 16:05
4

What does the `FIRST` do there? – Cees Timmerman Aug 25 '15 at 14:35
20

@Cees Timmerman: The keyword FIRST indicates that the new column will be the first column in the table. Alternatively, you can specify a particular position using AFTER existent_col_name. If neither FIRST nor AFTER are used then the new column is added after all current columns. – Ruben Ramirez Padron Sep 06 '15 at 02:19
what if I want to create an index on id but not a PK? – morpheus Feb 11 '16 at 17:59
@morpheus this adds a UNIQUE index which is an index in itself. I don't like having a column called id which isn't a PK though. – Arth Jun 15 '18 at 13:09
UNIQUE did the trick for me. Struggled for a while altering an table with existing primary keys but needed auto_increment. Thanks :) – Tower Jimmy Nov 02 '20 at 04:18

Bhaskar Bhatt · Answer 3 · 2017-07-05T11:56:36.180

46

If you want to add AUTO_INCREMENT in an existing table, need to run following SQL command:

 ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT primary key

edited Jul 05 '17 at 11:56

answered Feb 07 '13 at 14:24

Bhaskar Bhatt

1,399
13
19

1

that didn't work! Got this error: #1068 - Multiple primary key defined – Charles Jenkins Feb 07 '13 at 14:27
4

you get `Multiple primary key defined` because you already have a primary key on the table. A table can only have one primary key. Retry the above command on the table when it doesn't have a primary key – cchi Aug 16 '16 at 17:18
6

To avoid the "Multiple primary key" error, drop the existing primary key before defining the new one: `ALTER TABLE users DROP PRIMARY KEY, ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY` – Sean the Bean Sep 27 '17 at 19:48

score 28 · Answer 4 · answered Jan 30 '14 at 15:50

28

First you have to remove the primary key of the table

ALTER TABLE nametable DROP PRIMARY KEY

and now yo can add the autoincrement ...

ALTER TABLE nametable ADD id INT NOT NULL AUTO_INCREMENT PRIMARY KEY

answered Jan 30 '14 at 15:50

jrltt

651
10
23

cannot drop, then ADD id. Will get `Duplicate column name 'id'` – John Joe Mar 22 '17 at 01:57
3

@JohnJoe, that's because you already have the id column. Try this: ALTER TABLE nametable DROP PRIMARY KEY, MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY – TwoCode Nov 29 '18 at 11:18

score 20 · Answer 5 · edited Dec 05 '13 at 14:28

20

Well, you must first drop the auto_increment and primary key you have and then add yours, as follows:

-- drop auto_increment capability
alter table `users` modify column id INT NOT NULL;
-- in one line, drop primary key and rebuild one
alter table `users` drop primary key, add primary key(id);
-- re add the auto_increment capability, last value is remembered
alter table `users` modify column id INT NOT NULL AUTO_INCREMENT;

edited Dec 05 '13 at 14:28

voidstate

7,937
4
40
52

answered Feb 07 '13 at 14:27

echo_Me

37,078
5
58
78

1

got this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LTER TABLE users ADD id int NOT NULL AUTO_INCREMENT PRIMARY KEY(id)' at line 1 – Charles Jenkins Feb 07 '13 at 14:29
look your code u posted it miss LTER and not ALter , make sure u copied it all – echo_Me Feb 07 '13 at 14:31
thanks, but now i've got this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT PRIMARY KEY(id)' at line 3 sorry! – Charles Jenkins Feb 07 '13 at 14:38
got this: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(id)' at line 1 – Charles Jenkins Feb 07 '13 at 14:47
well you can follow those steps in my answer – echo_Me Feb 07 '13 at 14:54
You had 3 typos in this answer (modify not change, "id id" and comments need a space after them). I have edited the answer to fix them. – voidstate Dec 05 '13 at 14:27
mysql> ALTER TABLE domains modify COLUMN `id` INT AUTO_INCREMENT UNIQUE FIRST; Query OK, 2 rows affected (0.27 sec) Records: 2 Duplicates: 0 Warnings: 0 mysql> desc domains; +--------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +--------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | UNI | NULL | auto_increment | | domain | varchar(128) | NO | PRI | NULL | | +--------+--------------+------+-----+---------+----------------+ – beagle May 13 '16 at 08:01

score 18 · Answer 6 · answered Mar 12 '19 at 04:49

If you run the following command :

ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT PRIMARY KEY;

This will show you the error :

ERROR 1060 (42S21): Duplicate column name 'id'

This is because this command will try to add the new column named id to the existing table.

To modify the existing column you have to use the following command :

ALTER TABLE users MODIFY id int NOT NULL AUTO_INCREMENT PRIMARY KEY;

This should work for changing the existing column constraint....!

score 11 · Answer 7 · edited May 22 '18 at 13:33

11

Delete the primary key of a table if it exists:

 ALTER TABLE `tableName` DROP PRIMARY KEY;

Adding an auto-increment column to a table :

ALTER TABLE `tableName` ADD `Column_name` INT PRIMARY KEY AUTO_INCREMENT;

Modify the column which we want to consider as the primary key:

alter table `tableName` modify column `Column_name` INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

edited May 22 '18 at 13:33

serge.karalenka

980
1
15
29

answered May 22 '18 at 12:58

Sunny Verma

111
1
2

score 7 · Answer 8 · answered Apr 27 '21 at 14:44

7

Drop the primary index from the table:

ALTER TABLE `tableName` DROP INDEX `PRIMARY`;

Then add the id column (without a primary index). I have used a big int because I am going to have lots of data but INT(11) should work just as well:

ALTER TABLE `tableName` ADD COLUMN `id` BIGINT(11) NOT NULL FIRST;

Then modify the column with auto-increment (thanks php). It needs to be a primary key:

ALTER TABLE `tableName ` MODIFY COLUMN `id` BIGINT(11) UNSIGNED PRIMARY KEY AUTO_INCREMENT;

I have just tried this on a table of mine and it appears to have worked.

answered Apr 27 '21 at 14:44

user96279

101
1
5

It's also possible to perform the last 2 steps in a single query, though: ```ALTER TABLE PremiumPackages ADD COLUMN id MEDIUMINT(8) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT FIRST;``` – vdudouyt Jun 03 '21 at 06:21
it did not work – kta Feb 10 '22 at 06:20

score 6 · Answer 9 · answered Mar 22 '17 at 02:27

6

Just change the ADD to MODIFY and it will works !

Replace

ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT

To

ALTER TABLE users MODIFY id int NOT NULL AUTO_INCREMENT;

answered Mar 22 '17 at 02:27

John Joe

12,412
16
70
135

Odhik Susanto · Answer 10 · 2016-05-03T07:23:20.703

4

ALTER TABLE users CHANGE id int( 30 ) NOT NULL AUTO_INCREMENT

the integer parameter is based on my default sql setting have a nice day

edited May 03 '16 at 07:23

answered Jun 22 '13 at 18:46

Odhik Susanto

197
1
6

score 3 · Answer 11 · answered Jan 27 '14 at 04:40

3

ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT primary key FIRST

answered Jan 27 '14 at 04:40

php

4,307
1
24
13

score 1 · Answer 12 · answered Dec 16 '19 at 17:20

1

For PostgreSQL you have to use SERIAL instead of auto_increment.

ALTER TABLE your_table_name ADD COLUMN id SERIAL NOT NULL PRIMARY KEY

answered Dec 16 '19 at 17:20

PauloBorba

156
3
5

score 1 · Answer 13 · answered Jan 15 '21 at 16:26

1

This SQL request works for me :

ALTER TABLE users
CHANGE COLUMN `id` `id` INT(11) NOT NULL AUTO_INCREMENT ;

answered Jan 15 '21 at 16:26

CoulRaoul75

23
4

score 0 · Answer 14 · answered Nov 12 '18 at 13:43

0

ALTER TABLE `table` ADD `id` INT NOT NULL AUTO_INCREMENT unique

Try this. No need to drop your primary key.

answered Nov 12 '18 at 13:43

刘斌文

1

live-love · Answer 15 · 2022-07-01T18:43:17.397

0

If you want to add an id with a primary key and identity:

ALTER TABLE user ADD id INT NOT NULL AUTO_INCREMENT FIRST , ADD PRIMARY KEY (id);

edited Jul 01 '22 at 18:43

answered Jul 01 '22 at 18:37

live-love

48,840
22
240
204

score -1 · Answer 16 · edited Dec 13 '13 at 17:23

-1

Check for already existing primary key with different column. If yes, drop the primary key using:

ALTER TABLE Table1
DROP CONSTRAINT PK_Table1_Col1
GO

and then write your query as it is.

edited Dec 13 '13 at 17:23

Math

3,334
4
36
51

answered Dec 13 '13 at 17:02

user3100184

1

score -2 · Answer 17 · answered Aug 25 '14 at 09:23

Proceed like that :

Make a dump of your database first

Remove the primary key like that

ALTER TABLE yourtable DROP PRIMARY KEY

Add the new column like that

ALTER TABLE yourtable add column Id INT NOT NULL AUTO_INCREMENT FIRST, ADD primary KEY Id(Id)

The table will be looked and the AutoInc updated.

Add Auto-Increment ID to existing table?

17 Answers17

Linked

Related