MySQL: Add Auto-Increment Column to Existing Table


You can use the following syntax in MySQL to add an auto-increment column to an existing table:

ALTER TABLE athletes ADD COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

This particular example adds an integer column named id that contains the values 1, 2, 3, … to the last position of the table named athletes.

If you would like to add this column to the first position in the table, simply use the FIRST statement at the end of the line:

ALTER TABLE athletes ADD COLUMN id INT PRIMARY KEY AUTO_INCREMENT FIRST;

The following example shows how to use this syntax in practice.

Example: Add Column in First Position in MySQL

Suppose we create the following table named athletes that contains information about various basketball players:

-- create table 
CREATE TABLE athletes (
  team TEXT NOT NULL,
  points INT NOT NULL
);

-- insert rows into table
INSERT INTO athletes VALUES ('Mavs', 22);
INSERT INTO athletes VALUES ('Warriors', 14);
INSERT INTO athletes VALUES ('Nuggets', 37);
INSERT INTO athletes VALUES ('Lakers', 19);
INSERT INTO athletes VALUES ('Celtics', 26);

-- view all rows in table
SELECT * FROM athletes;

Output:

+----------+--------+
| team     | points |
+----------+--------+
| Mavs     |     22 |
| Warriors |     14 |
| Nuggets  |     37 |
| Lakers   |     19 |
| Celtics  |     26 |
+----------+--------+

Suppose that we would like to add a new column named id that simply contains the values 1, 2, 3, etc. to be used as an athlete identification number.

We can use the following syntax to do so:

-- add id column to last position in table
ALTER TABLE athletes ADD COLUMN id INT PRIMARY KEY AUTO_INCREMENT;

-- view all rows in updated table
SELECT * FROM athletes;

Output:

+----------+--------+----+
| team     | points | id |
+----------+--------+----+
| Mavs     |     22 |  1 |
| Warriors |     14 |  2 |
| Nuggets  |     37 |  3 |
| Lakers   |     19 |  4 |
| Celtics  |     26 |  5 |
+----------+--------+----+

Notice that a new column named id has been added to the last position in the table and it contains integer values starting at 1 and auto-incremented by 1.

If you would instead like to add this id column to the first position in the table, then you can use the following syntax:

-- add id column to first position in table
ALTER TABLE athletes ADD COLUMN id INT PRIMARY KEY AUTO_INCREMENT FIRST;

-- view all rows in updated table
SELECT * FROM athletes;

Output:

+----+----------+--------+
| id | team     | points |
+----+----------+--------+
|  1 | Mavs     |     22 |
|  2 | Warriors |     14 |
|  3 | Nuggets  |     37 |
|  4 | Lakers   |     19 |
|  5 | Celtics  |     26 |
+----+----------+--------+

Notice that the new column named id has been added to the first position in the table.

Additional Resources

The following tutorials explain how to perform other common tasks in MySQL:

MySQL: How to Add Column with Default Value
MySQL: How to Add Column in First Position
MySQL: How to Add Column After a Specific Column

Leave a Reply

Your email address will not be published. Required fields are marked *