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

Adding Auto-Increment ID to an Existing Table with Primary Key

Enhancing an existing table with an auto-increment ID is a common requirement in database management. However, adding an auto-increment column to a table that already has a primary key can present challenges.

When you attempt to add an auto-increment column using the statement:

ALTER TABLE users
ADD id int NOT NULL AUTO_INCREMENT

you may encounter the error:

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

This is because MySQL allows only one auto-increment column per table, and it must be designated as a primary key.

To resolve this issue, you need to first modify the existing primary key to allow auto-increment, and then add the new auto-increment column. Here's an example:

ALTER TABLE `users`
MODIFY COLUMN `id` INT NOT NULL AUTO_INCREMENT,
ADD COLUMN `name` VARCHAR(255) NOT NULL

By executing this query, you modify the existing id column to be auto-increment and add a new name column to the table. The id column will now serve as both the primary key and the auto-increment ID.

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