How to Add an Auto-Increment ID to a Table with an Existing Primary Key?

How to Add an Auto-Increment ID to an Existing Table

When maintaining a database, it may become necessary to add an auto-increment column to an existing table. However, this task can present challenges when there is already a primary key defined.

Original Issue:

A developer experienced an error when attempting to add an auto-increment ID to a table named "users" using the following syntax:

ALTER TABLE users
ADD id int NOT NULL AUTO_INCREMENT

The error encountered was:

#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

Solution:

To successfully add an auto-increment ID to a table with an existing primary key, use the following syntax:

ALTER TABLE `users` ADD `id` INT NOT NULL AUTO_INCREMENT;

This modified syntax correctly defines the auto-increment column as the primary key, resolving the error.

The above is the detailed content of How to Add an Auto-Increment ID to a Table with an Existing Primary Key?. For more information, please follow other related articles on the PHP Chinese website!