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,
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;
id is added successfully. Lets see the contents of the modified table.
Let us see the updated table schema.