AUTO_INCREMENT for PRIMARY KEY
We can add a new column like an id, that auto increments itself for every row that is inserted to the table.
In this MySQL Tutorial, we shall create a new column that is PRIMARY KEY with AUTO_INCREMENT column modifier.
To add a new column to MySQL, following is the syntax of the SQL Query:
ALTER TABLE table_name ADD [COLUMN] new_column_name AUTO_INCREMENT PRIMARY KEY;
Example 1 – AUTO_INCREMENT for PRIMARY KEY
For this example, let us consider the following table, students
.
ADVERTISEMENT
![MySQL add new column that auto increments](https://www.tutorialkart.com/wp-content/uploads/2018/12/mysql-add-new-column-that-auto-increments.png)
Run the following SQL Query to add a new column named id
that acts as PRIMARY KEY and auto increments for each insert.
ALTER TABLE students ADD id INT AUTO_INCREMENT PRIMARY KEY;
![MySQL add integer column that is PRIMARY KEY and auto increments](https://www.tutorialkart.com/wp-content/uploads/2018/12/mysql-add-column-auto-increment-primary-key-1.png)
The column id
is added successfully. Lets see the contents of the modified table.
![MySQL new column added](https://www.tutorialkart.com/wp-content/uploads/2018/12/mysql-new-column-added.png)
Let us see the updated table schema.
![Updated table schema when new primary column is added](https://www.tutorialkart.com/wp-content/uploads/2018/12/mysql-new-column-added-updated-schema.png)