Where is auto increment column in mysql?

228

New! Save questions or answers and organize your favorite content.
Learn more.

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

TRiG

9,8346 gold badges55 silver badges106 bronze badges

asked Jan 30, 2010 at 19:16

ALTER TABLE document MODIFY COLUMN document_id INT auto_increment

answered Jan 30, 2010 at 19:19

romanroman

10.9k1 gold badge28 silver badges42 bronze badges

5

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

answered Jan 30, 2010 at 19:22

Roland BoumanRoland Bouman

30.4k6 gold badges65 silver badges67 bronze badges

2

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;

TRiG

9,8346 gold badges55 silver badges106 bronze badges

answered Aug 11, 2015 at 4:02

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.

answered Jan 30, 2010 at 19:32

Steven OxleySteven Oxley

6,3876 gold badges41 silver badges54 bronze badges

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.

Unmitigated

49.9k7 gold badges49 silver badges65 bronze badges

answered Jan 30, 2010 at 19:22

Håvard SHåvard S

22.5k6 gold badges59 silver badges71 bronze badges

1

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)

answered Jan 30, 2010 at 19:21

Dan SoapDan Soap

9,9841 gold badge40 silver badges49 bronze badges

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;

answered Nov 24, 2020 at 8:29

You can do it like this:

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

Unmitigated

49.9k7 gold badges49 silver badges65 bronze badges

answered Apr 12, 2018 at 6:50

1

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, 2018 at 5:22

Azhar ZafarAzhar Zafar

1,2849 silver badges13 bronze badges

1

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;

Unmitigated

49.9k7 gold badges49 silver badges65 bronze badges

answered Jul 5, 2015 at 17:30

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)

fedorqui

260k99 gold badges527 silver badges578 bronze badges

answered Dec 7, 2014 at 17:39

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 ;

Unmitigated

49.9k7 gold badges49 silver badges65 bronze badges

answered Dec 19, 2014 at 21:06

SusieSusie

5,00010 gold badges51 silver badges74 bronze badges

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, 2018 at 12:25

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;

solarissmoke

28.6k13 gold badges65 silver badges70 bronze badges

answered Jul 16, 2016 at 6:07

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;

Rob♦

26.7k15 gold badges81 silver badges94 bronze badges

answered Aug 18, 2017 at 13:47

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

Pang

9,222146 gold badges85 silver badges118 bronze badges

answered Feb 24, 2017 at 19:12

Aminur RahmanAminur Rahman

3801 gold badge4 silver badges13 bronze badges

Just like this:

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

answered Jul 9, 2018 at 18:54

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;

answered Dec 8, 2017 at 12:52

OptimizerOptimizer

6477 silver badges7 bronze badges

Try the following:

ALTER TABLE table_name MODIFY COLUMN id datatype auto_increment;

xKobalt

1,4982 gold badges12 silver badges19 bronze badges

answered Mar 13, 2019 at 6:16

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 

answered Jan 7, 2021 at 7:55

Use the following queries:

ALTER TABLE YourTable DROP COLUMN IDCol

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

answered Jun 19, 2017 at 17:44

How do I make a column auto increment in MySQL?

Where is auto increment in SQL?

How do I add auto increment to a column?

What is auto increment in MySQL?