How to Add Auto Increment Column in Existing Table in MySQL

Auto Increment columns automatically increase in value as you add more rows to the table. In this article we will look at how to add auto increment column in MySQL.


How to Add Auto Increment Column

Here are the steps to add auto increment column in MySQL. Let’s say you have the following sales(id, amount) table.

mysql> create table sales(id int, amount int);

mysql> insert into sales(id,amount) 
       values(1, 100),(4,300),(6,400);

mysql> select * from sales;
+------+--------+
| id   | amount |
+------+--------+
|    1 |    100 |
|    4 |    300 |
|    6 |    400 |
+------+--------+

Now, we will modify the id column to be auto increment, using ALTER TABLE.

Bonus Read : Top 5 Free Database Design Tools

Here’s the syntax of ALTER TABLE statement,

ALTER TABLE table_name 
MODIFY column_name INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

In the above statement, you need to specify the table_name and column_name.

Here’s the SQL statement to add AUTO INCREMENT constraint to id column.

ALTER TABLE sales
MODIFY id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

Next we will add a couple of rows in sales table.

mysql> insert into sales(amount) values(150),(250);

mysql> select * from sales;
+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  4 |    300 |
|  6 |    400 |
|  7 |    150 |
|  8 |    250 |
+----+--------+

As you can see, the MySQL has automatically increased and populated id column with values 7 and 8.

Bonus Read : How to Find Nth Row in MySQL

You can also add auto increment column during table creation. However, remember that auto increment constraint can be assigned only to primary key column.

Here’s the syntax to add auto increment column during table creation.

create table table_name(
auto_increment_column_name int not null auto_increment primary key,
column2,
...);

Bonus Read : How to Replicate MySQL Database

Here’s an example to add auto increment column in MySQL

mysql> create table sales2(id int not null auto_increment primary key, 
amount int);

mysql> insert into sales2(amount) values(100),(125),(250),(300);

mysql> select * from sales2;
+----+--------+
| id | amount |
+----+--------+
|  1 |    100 |
|  2 |    125 |
|  3 |    250 |
|  4 |    300 |
+----+--------+

As you can see above, the id column is automatically incremented and populated.


How to Set Auto Increment Initial Value

By default, auto increment column value starts from 1. You can change auto increment start value if you want. Here’s the syntax for it,

alter table table_name AUTO_INCREMENT=increment_value

In the above SQL query, you need to specify the table_name as well as increment_value.

For example, here’s the SQL query to set initial increment value to 100

alter table sales AUTO_INCREMENT=100

Hopefully, the above article will help you add auto increment column in existing table in MySQL.

Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.

mm

About Ubiq

Ubiq is a dashboard & reporting platform for small & medium businesses. Ubiq makes it easy to build dashboards & reports for your business. Try it for free today!