Table of Contents
To add an auto-increment column to an existing table in MySQL, you would use the ALTER TABLE statement with the ADD keyword. Within the parentheses, you would specify the name of the column, the data type, and the AUTO_INCREMENT attribute. This will ensure that each time a new record is inserted into the table, the value of this column will automatically increment by 1. You can also specify a starting value and an increment value if desired. This allows for easier and more accurate management of primary keys in the 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.
-- 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: