Add an AUTO_INCREMENT column as PRIMARY KEY in MySQL table

Add an AUTO_INCREMENT Primary Key to an Existing MySQL Table

Use ALTER TABLE to add an integer column that MySQL fills automatically for new rows and uses as the table’s primary key. This is commonly used when an existing table does not already have a stable, unique row identifier.

For example, a new id column can be defined with AUTO_INCREMENT and PRIMARY KEY. MySQL then generates a unique sequence value whenever an insert omits the id column or supplies NULL.

Requirements Before Adding an AUTO_INCREMENT PRIMARY KEY

• The table must not already have a primary key unless you intentionally replace it.
• A table can have only one AUTO_INCREMENT column.
• The auto-increment column must use an appropriate numeric type and must be indexed. Declaring it as the primary key satisfies the indexing requirement.
• Choose a type with enough range for future rows. INT UNSIGNED is suitable for many tables, while BIGINT UNSIGNED provides a larger range.
• Review foreign keys and application queries before changing the primary key of a production table.

Check the current definition before running the alteration:

SHOW CREATE TABLE students;

MySQL ALTER TABLE Syntax for an AUTO_INCREMENT Primary Key

The original shorthand for adding the column is shown below. A runnable statement must also specify a numeric data type such as INT or BIGINT.

ALTER TABLE table_name
ADD [COLUMN] new_column_name AUTO_INCREMENT PRIMARY KEY;

Use the following complete syntax in an actual query:

ALTER TABLE table_name
ADD COLUMN new_column_name BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

The COLUMN keyword is optional. Add FIRST to place the new key at the beginning of the table definition, or use AFTER existing_column to choose another position. Column position does not change primary-key behavior.

Choosing INT or BIGINT for the AUTO_INCREMENT Column

Use the smallest integer type that safely covers the expected number of rows. An unsigned type avoids reserving half of the range for negative values, which are not normally useful for generated identifiers.

Example: Add id AUTO_INCREMENT PRIMARY KEY to students

For this example, consider the following students table, which does not yet have an id primary key.

Run the following SQL query to add a new column named id that acts as the primary key and auto-increments for each insert.

 ALTER TABLE students
 ADD id INT AUTO_INCREMENT PRIMARY KEY;

The id column is added successfully. For rows already in the table, MySQL assigns generated values while performing the table alteration. Treat those values as identifiers; do not assume that they reflect a meaningful business or chronological order.

The updated schema shows that id is an integer primary key with the auto_increment attribute.

Verify the New AUTO_INCREMENT Primary Key

Use DESCRIBE for a compact column summary and SHOW CREATE TABLE for the complete definition.

DESCRIBE students;

SHOW CREATE TABLE students;

In the output, the id row should show PRI under the key column and auto_increment under extra information. The full table definition should include PRIMARY KEY (id).

Insert Rows Without Supplying the AUTO_INCREMENT id

After the alteration, omit id from normal insert statements. MySQL generates the next value automatically.

INSERT INTO students (name)
VALUES ('Asha');

SELECT LAST_INSERT_ID();

LAST_INSERT_ID() returns the value generated by the current connection for its most recent insert. When one statement inserts multiple rows, it returns the first generated value from that statement.

Set the Next AUTO_INCREMENT Value in MySQL

To start future generated values from a higher number, set the table’s next auto-increment value:

ALTER TABLE students AUTO_INCREMENT = 1000;

The next generated value will be at least 1000. MySQL does not lower the counter below the value required by the largest existing id; if the table already contains a greater value, the next number follows that maximum.

Fix Common Errors When Adding the AUTO_INCREMENT Primary Key

Multiple primary key defined

This error means the table already has a primary key. Inspect it with SHOW CREATE TABLE. Do not drop an existing primary key until you have checked foreign-key references, uniqueness requirements, and application code that depends on it.

If the existing primary key must remain, the new auto-increment column can instead use a unique index:

ALTER TABLE students
ADD COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT UNIQUE;

This creates an auto-generated unique identifier, but it does not make id the primary key.

Incorrect table definition for an AUTO_INCREMENT column

Confirm that the table does not already contain another AUTO_INCREMENT column and that the new column is indexed. Also avoid defining a separate default value for the auto-increment column.

ALTER TABLE takes a long time or blocks writes

Adding a primary key to an existing InnoDB table can require substantial table work, especially when the table contains many rows. Test the operation on a copy, take a verified backup, estimate the maintenance impact, and schedule the change according to the database’s availability requirements.

MySQL AUTO_INCREMENT Primary Key FAQs

Can a MySQL table have more than one AUTO_INCREMENT column?

No. MySQL permits only one AUTO_INCREMENT column per table, and that column must be indexed.

Must an AUTO_INCREMENT column be the primary key?

No. It must be indexed, but the index can be a unique key instead of the primary key. Using it as the primary key is a common design when the column is the table’s main row identifier.

What happens to existing rows when the id column is added?

MySQL assigns generated values to the existing rows during the alteration. The values are unique, but their assignment order should not be treated as a guaranteed chronological or business order.

Why does ALTER TABLE report “Multiple primary key defined”?

A table can have only one primary key. The error appears when the table already has one and the statement attempts to add another. Keep the existing key, replace it carefully, or add the new column with a unique index instead.

How do I change the next AUTO_INCREMENT number?

Run ALTER TABLE table_name AUTO_INCREMENT = value;. The requested value must be greater than the values already stored in the auto-increment column.

MySQL AUTO_INCREMENT Primary Key QA Checklist

• Confirm that the table has no existing primary key, or document the approved replacement plan.
• Confirm that no other column already uses AUTO_INCREMENT.
• Use an integer type with enough range for the expected row count.
• Verify the result with both DESCRIBE and SHOW CREATE TABLE.
• Test an insert that omits id and confirm the generated value with LAST_INSERT_ID().
• For a populated production table, verify the backup and assess locking or rebuild impact before running ALTER TABLE.

Result of Adding the AUTO_INCREMENT Primary Key

The students table now has an id column that uniquely identifies each row. Existing rows receive generated identifiers, and future inserts receive the next value automatically when id is omitted.