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.