Alter a MySQL column to be AUTO_INCREMENT

Question

I’m trying to modify a table to make its primary key column AUTO_INCREMENT after the fact. I have tried the following SQL, but got a syntax error notification.

ALTER TABLE document
ALTER COLUMN document_id AUTO_INCREMENT

Am I doing something wrong or is this not possible?

+--------------------+
| VERSION()          |
+--------------------+
| 5.0.75-0ubuntu10.2 |
+--------------------+

roman · Accepted Answer · 2014-03-11T21:07:35.047

455

ALTER TABLE document MODIFY COLUMN document_id INT auto_increment

edited Mar 11 '14 at 21:07

answered Jan 30 '10 at 19:19

roman

11,143
1
31
42

3

I'm curious why you would suggest INT(4). Any particular reason? – Steven Oxley Jan 30 '10 at 19:34
3

@Steven Oxley because I declared my table that way. – C. Ross Jan 30 '10 at 19:48
34

Specifying a number between parenthesis does exactly *nothing* (well ok, almost nothing) for MySQL integer types. The only thing that may be influenced by the number is the display width, and it is up to the client to do that. But don't be deceived and think that it works like it does for VARCHAR and DECIMAL types - in those cases, the amount of data you can store in there is actually specified, whereas a particular flavour of INT is always allows storage of the exact same range of values – Roland Bouman Jan 30 '10 at 20:03
I always get an primary key error while doing this operations, so I have this is what I do: SET FOREIGN_KEY_CHECKS = 0; ALTER TABLE document DROP PRIMARY KEY; ALTER TABLE document MODIFY COLUMN document_id INT PRIMARY KEY AUTO_INCREMENT NOT NULL; SET FOREIGN_KEY_CHECKS = 1; – Fernando Jul 13 '19 at 15:50
This doesn't seem to work in MySQL because you need to set the column as a primary key since only one column is allowed to be auto_increment (also see [Roland Bouman's answer below](https://stackoverflow.com/a/2169099/1440546) – charlchad Sep 15 '19 at 04:51

score 91 · Answer 2 · edited Jan 05 '13 at 00:12

91

Roman is right, but note that the auto_increment column must be part of the PRIMARY KEY or a UNIQUE KEY (and in almost 100% of the cases, it should be the only column that makes up the PRIMARY KEY):

ALTER TABLE document MODIFY document_id INT AUTO_INCREMENT PRIMARY KEY

edited Jan 05 '13 at 00:12

pawstrong

924
7
17

answered Jan 30 '10 at 19:22

Roland Bouman

31,125
6
66
67

12

+1. Primary key required if not defined at the time of table creation. – Apr 13 '12 at 09:07
1

Also this means that data in that column needs to be already unique – Umair Malhi Mar 16 '18 at 06:30

score 13 · Answer 3 · edited May 14 '18 at 16:29

13

In my case it only worked when I put not null. I think this is a constraint.

ALTER TABLE document MODIFY COLUMN document_id INT NOT NULL AUTO_INCREMENT;

edited May 14 '18 at 16:29

TRiG

10,148
7
57
107

answered Aug 11 '15 at 04:02

Rabia Naz khan

507
4
13

Meisam Rasouli · Answer 4 · 2020-11-24T10:51:04.937

12

You can apply the atuto_increment constraint to the data column by the following query:

ALTER TABLE customers MODIFY COLUMN customer_id BIGINT NOT NULL AUTO_INCREMENT;

But, if the columns are part of a foreign key constraint you, will most probably receive an error. Therefore, it is advised to turn off foreign_key_checks by using the following query:

SET foreign_key_checks = 0;

Therefore, use the following query instead:

SET foreign_key_checks = 0;
ALTER TABLE customers MODIFY COLUMN customer_id BIGINT NOT NULL AUTO_INCREMENT;
SET foreign_key_checks = 1;

edited Nov 24 '20 at 10:51

answered Nov 24 '20 at 08:29

Meisam Rasouli

301
2
5

This should be the accepted answer. In real-life situations the PK is very likely to be part of a FK constraint. – E-Riz Aug 16 '23 at 13:08

score 8 · Answer 5 · edited Aug 11 '18 at 19:20

8

You must specify the type of the column before the auto_increment directive, i.e. ALTER TABLE document MODIFY COLUMN document_id INT AUTO_INCREMENT.

edited Aug 11 '18 at 19:20

Unmitigated

76,500
11
62
80

answered Jan 30 '10 at 19:22

Håvard S

23,244
8
61
72

1

It should be `MODIFY COLUMN` – shxfee Sep 24 '14 at 23:45

score 8 · Answer 6 · answered Jan 30 '10 at 19:32

The SQL to do this would be:

ALTER TABLE `document` MODIFY COLUMN `document_id` INT AUTO_INCREMENT;

There are a couple of reasons that your SQL might not work. First, you must re-specify the data type (INT in this case). Also, the column you are trying to alter must be indexed (it does not have to be the primary key, but usually that is what you would want). Furthermore, there can only be one AUTO_INCREMENT column for each table. So, you may wish to run the following SQL (if your column is not indexed):

ALTER TABLE `document` MODIFY `document_id` INT AUTO_INCREMENT PRIMARY KEY;

You can find more information in the MySQL documentation: http://dev.mysql.com/doc/refman/5.1/en/alter-table.html for the modify column syntax and http://dev.mysql.com/doc/refman/5.1/en/create-table.html for more information about specifying columns.

score 7 · Answer 7 · answered Jan 30 '10 at 19:21

AUTO_INCREMENT is part of the column's datatype, you have to define the complete datatype for the column again:

ALTER TABLE document
ALTER COLUMN document_id int AUTO_INCREMENT

(int taken as an example, you should set it to the type the column had before)

score 6 · Answer 8 · edited Aug 11 '18 at 19:21

6

You can do it like this:

 alter table [table_name] modify column [column_name] [column_type] AUTO_INCREMENT;

edited Aug 11 '18 at 19:21

Unmitigated

76,500
11
62
80

answered Apr 12 '18 at 06:50

Aditya Kumar

61
1
1

This seems to be just a repeat of all the other answers. – Pang Apr 12 '18 at 07:15

score 5 · Answer 9 · edited Aug 11 '18 at 19:20

5

Below statement works. Note that you need to mention the data type again for the column name (redeclare the data type the column was before).

ALTER TABLE document
MODIFY COLUMN document_id int AUTO_INCREMENT;

edited Aug 11 '18 at 19:20

Unmitigated

76,500
11
62
80

answered Jul 05 '15 at 17:30

user5082888

51
1
1

score 5 · Answer 10 · answered Apr 22 '18 at 05:22

5

You can use the following query to make document_id to increment automatically

ALTER TABLE document MODIFY COLUMN document_id INT auto_increment

It is preferred to make document_id primary key as well

ALTER TABLE document MODIFY COLUMN document_id INT auto_increment PRIMARY KEY;

answered Apr 22 '18 at 05:22

Azhar Zafar

1,554
1
10
13

1

Note that if that column is already a primary key you can't declare it again there else it gives error about multiple primary keys (you'd expect it to be a bit more clever there and see that it's the same primary key I guess). So in that case you just skip the PRIMARY KEY and it doesn't affect the existing primary key setting on that column – George Birbilis May 18 '18 at 02:08

score 4 · Answer 11 · answered Aug 21 '18 at 12:25

4

Setting column as primary key and auto_increment at the same time:

  mysql> ALTER TABLE persons MODIFY COLUMN personID INT auto_increment PRIMARY KEY;
    Query OK, 10 rows affected (0.77 sec)
    Records: 10  Duplicates: 0  Warnings: 0

    mysql>

answered Aug 21 '18 at 12:25

Nesimi Taghizade

111
1
3

score 3 · Answer 12 · edited Dec 07 '14 at 17:56

3

AUTO_INCREMENT is part of the column's datatype, you have to define the complete datatype for the column again:

ALTER TABLE document
MODIFY COLUMN document_id int AUTO_INCREMENT

(int taken as an example, you should set it to the type the column had before)

edited Dec 07 '14 at 17:56

fedorqui

275,237
103
548
598

answered Dec 07 '14 at 17:39

KKK

31
1

score 3 · Answer 13 · edited Aug 11 '18 at 19:21

3

If none of the above works try this. This is what I did in MYSQL and yes, you need to write the column name (document_id) twice.

ALTER TABLE document
CHANGE COLUMN document_id document_id INT(11) NOT NULL AUTO_INCREMENT ;

edited Aug 11 '18 at 19:21

Unmitigated

76,500
11
62
80

answered Dec 19 '14 at 21:06

Susie

5,038
10
53
74

score 2 · Answer 14 · edited Jul 16 '16 at 06:20

To modify the column in mysql we use alter and modify keywords. Suppose we have created a table like:

create table emp(
    id varchar(20),
    ename varchar(20),
    salary float
);

Now we want to modify type of the column id to integer with auto increment. You could do this with a command like:

alter table emp modify column id int(10) auto_increment;

score 2 · Answer 15 · edited Apr 12 '18 at 07:14

2

alter table tbl_user MODIFY COLUMN id int(10) auto_increment;

edited Apr 12 '18 at 07:14

Pang

9,564
146
81
122

answered Feb 24 '17 at 19:12

Aminur Rahman

400
1
6
14

score 2 · Answer 16 · edited Aug 18 '17 at 13:48

2

Previous Table syntax:

CREATE TABLE apim_log_request (TransactionId varchar(50) DEFAULT NULL);

For changing the TransactionId to auto increment use this query

ALTER TABLE apim_log_request MODIFY COLUMN TransactionId INT auto_increment;

edited Aug 18 '17 at 13:48

Rob

26,989
16
82
98

answered Aug 18 '17 at 13:47

Usman Yaqoob

535
5
13

score 2 · Answer 17 · answered Jul 09 '18 at 18:54

2

Just like this:

alter table document modify column id int(11) auto_increment;

answered Jul 09 '18 at 18:54

apemost

41
4

Optimizer · Answer 18 · 2017-12-08T16:17:57.683

1

As you are redefining the column again, you have to specify the datatype again and add auto_increment to it as it's a part of datatype.

ALTER TABLE `document` MODIFY COLUMN `document_id` INT AUTO_INCREMENT;

edited Dec 08 '17 at 16:17

answered Dec 08 '17 at 12:52

Optimizer

687
7
7

score 1 · Answer 19 · edited Jul 29 '20 at 10:15

1

Try the following:

ALTER TABLE table_name MODIFY COLUMN id datatype auto_increment;

edited Jul 29 '20 at 10:15

xKobalt

1,498
2
13
19

answered Mar 13 '19 at 06:16

Kartik Madnani

11
2

score -1 · Answer 20 · answered Jan 07 '21 at 07:55

Since SQL tag is attached to the question I really think all answers missed one major point.

MODIFY command does not exist in SQL server So you will be getting an error when you run the

ALTER TABLE Satellites MODIFY COLUMN SatelliteID INT auto_increment PRIMARY KEY;

In this case you can either add new column as INT IDENTITY

ALTER TABLE Satellites
   ADD ID INT IDENTITY
       CONSTRAINT PK_YourTable PRIMARY KEY CLUSTERED;

OR

Fill the existing null index with incremental numbers using this method,

DECLARE @id INT
SET @id = 0 
UPDATE Satellites SET @id = SatelliteID = @id + 1

score -3 · Answer 21 · edited Jun 19 '17 at 18:19

-3

Use the following queries:

ALTER TABLE YourTable DROP COLUMN IDCol

ALTER TABLE YourTable ADD IDCol INT IDENTITY(1,1)

edited Jun 19 '17 at 18:19

Chris Forrence

10,042
11
48
64

answered Jun 19 '17 at 17:44

Doug Cobain

1

Alter a MySQL column to be AUTO_INCREMENT

21 Answers21

Setting column as primary key and auto_increment at the same time:

OR

Linked

Related