How to Add Auto Increment Column in Existing Table in MySQL

Last updated on September 4th, 2024 at 06:27 am

Auto Increment columns automatically increase in value as you add more rows to the table. They are unique and sequential. They are generally defined at the time of table creation. But sometimes database administrators need to add such a column to an existing table. In this article we will look at how to add auto increment column in MySQL.

Why Add Auto Increment Column to Table

Auto Increment columns are very useful since they are automatically populated with unique sequential numbers, one for each row. They act as a primary key or unique identifier for each row making it very easy to quickly select the specific row, if needed. Every database system supports auto increment columns and ensure unique values are automatically stored in them.

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

There are 2 ways to add auto increment column – to an existing table, or during table creation. We will look at both these methods.

Add Auto Increment Column to Existing Table

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. Please note, you can add auto increment constraint only to the primary key column of your table. Therefore, when you add such a column, you need to mention both AUTO_INCREMENT and ‘PRIMARY KEY’ together.

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

Add Auto Increment Column to New Table

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

Here’s the syntax to add auto increment column during table creation. It needs to be mentioned as a column constraint.

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

Conclusion

In this article, we have learnt how to include an auto increment column to existing table in MySQL. There are two ways to add auto increment column. You can either add it during table creation or afterwards. We have learnt both these methods. Adding such a column may need you to re-think about your table indexes and constraints. 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.