Alter a MySQL column to be AUTO_INCREMENT?

---

Let’s say we have a table and now there is a requirement to add AUTO_INCREMENT on column name. For that, use the MODIFY command.

Here, we will create a demo table first.

mysql>  create table AddingAutoIncrement
   -> (
   -> Id int,
   -> Name varchar(200),
   -> Primary key(Id)
   -> );
Query OK, 0 rows affected (0.47 sec)

We have created a table above and now let us alter the table to add AUTO_INCREMENT on column name ‘Id’. The syntax is as follows −

alter table yourTableNamet modify yourColumnName int AUTO_INCREMENT;

Apply the above syntax to add AUTO_INCREMENT. The query is as follows.

mysql>  ALTER table AddingAutoIncrement modify Id int AUTO_INCREMENT;
Query OK, 0 rows affected (1.19 sec)
Records: 0  Duplicates: 0  Warnings: 0

Above, we have added “AUTO_INCREMENT” on column name ‘Id’. Let us check the same with the help of DESC command. The query is as follows −

mysql> desc AddingAutoIncrement;

Sample Output.

+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| Id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| Name  | varchar(200) | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

Look at the output above and column name ‘Extra’. In the column name ‘Extra’, there is a keyword auto_increment. This itself says that we have added the keyword successfully.

Now, I am going to insert records and check whether the row is increment by one or not. The query is as follows −

mysql> insert into AddingAutoIncrement(Name) values('John');
Query OK, 1 row affected (0.20 sec)

mysql>  insert into AddingAutoIncrement(Name) values('Smith');
Query OK, 1 row affected (0.12 sec)

mysql>  insert into AddingAutoIncrement(Name) values('Bob');
Query OK, 1 row affected (0.10 sec)

Display all records with the help of SELECT statement.

mysql> select *from AddingAutoIncrement;

The following is the output.

+----+-------+
| Id | Name  |
+----+-------+
|  1 | John  |
|  2 | Smith |
|  3 | Bob   |
+----+-------+
3 rows in set (0.00 sec)

As you can see in the above output, row is incremented by 1.

• Related Questions & Answers
• MySQL ALTER column to remove primary key and auto_increment?
• How to find if a column is auto_increment in MySQL?
• How to add column using alter in MySQL?
• Alter a table column from VARCHAR to NULL in MySQL
• How do I alter a MySQL table column defaults?
• AUTO_INCREMENT in MySQL can be signed by default?
• How to alter a MySQL Column from varchar(30) to varchar(100)?
• How to insert own values into auto_increment column in MySQL?
• How can we apply AUTO_INCREMENT to a column?
• What MySQL returns when we alter AUTO_INCREMENT value which is less than current sequence number?
• How to alter the data type of a MySQL table’s column?
• How to alter column type of multiple columns in a single MySQL query?
• Insertion in a MySQL table with only a single column set as auto_increment?
• Reset AUTO_INCREMENT in MySQL
• MySQL AUTO_INCREMENT with Examples