Add an AUTO_INCREMENT column as PRIMARY KEY in MySQL table

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.

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;

The column id is added successfully. Lets see the contents of the modified table.

Let us see the updated table schema.