By Charles Jenkins


2013-02-07 14:20:39 8 Comments

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?:)

13 comments

@Rajat Dabade 2019-03-12 04:49:46

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....!

@刘斌文 2018-11-12 13:43:04

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

Try this. No need to drop your primary key.

@Sunny Verma 2018-05-22 12:58:14

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;

@Bhaskar Bhatt 2013-02-07 14:24:59

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

@Charles Jenkins 2013-02-07 14:27:09

that didn't work! Got this error: #1068 - Multiple primary key defined

@cchi 2016-08-16 17:18:49

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

@Sean the Bean 2017-09-27 19:48:10

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

@John Joe 2017-03-22 02:27:22

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;

@Odhik Susanto 2013-06-22 18:46:54

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

@Grzegorz Piwowarek 2013-06-22 19:06:11

You could improve formatting

@Fandi Susanto 2015-09-01 09:44:50

This should be the right answer.

@Muhammad Asif Mahmood 2013-02-07 14:31:00

Try this

ALTER TABLE `users` ADD `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY

@Charles Jenkins 2013-02-07 14:35:08

#1068 - Multiple primary key defined

@Greg A 2015-08-04 13:35:25

In SQL Server 2008, replacing 'AUTO_INCREMENT' with 'IDENTITY(1,1)' worked for me.

@Tobias Baumeister 2016-10-07 11:32:24

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.

@Jonathan Laliberte 2017-02-05 12:51:42

works without the ' ' Thanks!

@Sean the Bean 2017-09-27 19:52:06

@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

@Tahir Raza 2018-07-04 14:43:15

To make it the first column, use FIRST e.g ALTER TABLE tbl1 ADD id INT PRIMARY KEY AUTO_INCREMENT FIRST

@Juhan 2014-08-25 09:23:11

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.

@jrltt 2014-01-30 15:50:27

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

@John Joe 2017-03-22 01:57:12

cannot drop, then ADD id. Will get Duplicate column name 'id'

@TwoCode 2018-11-29 11:18:26

@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

@php 2014-01-27 04:40:23

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

@user3100184 2013-12-13 17:02:52

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.

@echo_Me 2013-02-07 14:27:45

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;

@Charles Jenkins 2013-02-07 14:29:18

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

@echo_Me 2013-02-07 14:31:51

look your code u posted it miss LTER and not ALter , make sure u copied it all

@Charles Jenkins 2013-02-07 14:38:38

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 2013-02-07 14:47:01

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

@echo_Me 2013-02-07 14:54:22

well you can follow those steps in my answer

@voidstate 2013-12-05 14:27:09

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.

@beagle 2016-05-13 08:01:05

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 | | +--------+--------------+------+-----+---------+------------‌​----+

@Coderer 2013-06-27 15:00:20

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.

@reflexiv 2013-07-04 16:05:29

This worked for me and set the auto_increment id column as the primary key.

@Cees Timmerman 2015-08-25 14:35:04

What does the FIRST do there?

@Ruben Ramirez Padron 2015-09-06 02:19:21

@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.

@morpheus 2016-02-11 17:59:15

what if I want to create an index on id but not a PK?

@Arth 2018-06-15 13:09:45

@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.

@bksi 2018-09-19 17:07:03

dude, you saved me a LOT OF TIME. Thanks

Related Questions

Sponsored Content

10 Answered Questions

[SOLVED] Insert into a MySQL table or update if exists

37 Answered Questions

7 Answered Questions

2 Answered Questions

[SOLVED] SQL create table and set auto increment value without Alter table

  • 2015-04-07 08:16:23
  • Blue Fishy
  • 13137 View
  • 3 Score
  • 2 Answer
  • Tags:   mysql create-table

3 Answered Questions

Existing table add auto increment primary key - sql

  • 2016-05-16 05:43:37
  • Ankit Sompura
  • 669 View
  • -1 Score
  • 3 Answer
  • Tags:   mysql sql

1 Answered Questions

Add Auto-Increment ID to existing ascending column?

  • 2016-04-04 05:01:31
  • Abrar Ahmed
  • 85 View
  • 0 Score
  • 1 Answer
  • Tags:   mysql sql

0 Answered Questions

Auto_increment in child tables

0 Answered Questions

2 Answered Questions

Sponsored Content