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

Adding an Auto-Increment Column to an Existing Table

Creating an auto-increment column in an existing table can encounter errors, namely the "#1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key" message. This occurs when the table already possesses a primary key.

Solution:

To resolve this issue, follow these steps:

1. Run the following command to add an auto-increment column to the table:

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

2. This command assumes that you have an existing primary key in the table. If not, you need to create one first, for instance:

ALTER TABLE `users` ADD PRIMARY KEY (`id`);

Note that the above commands use backticks (``) to enclose the table and column names, which is required for certain database systems.

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