How to Automatically Add Auto-Increment Primary Keys to Existing Database Rows?

How to Automatically Assign Auto-Increment Primary Keys to Existing Rows

In situations where an existing table lacks a primary key and auto-increment column, adding these features can be necessary. However, manually assigning IDs to existing rows can be tedious and error-prone. This article provides a solution to this problem by guiding you through the process of automatically inserting auto-increment primary key and values into an existing table.

To add an auto-increment primary key column, use the ALTER TABLE statement with the ADD clause:

ALTER TABLE tbl ADD id INT PRIMARY KEY AUTO_INCREMENT;

This statement will create an id column as the primary key and set the AUTO_INCREMENT property, ensuring that new rows will be assigned unique IDs.

To automatically insert values into the primary key column, execute the above ALTER TABLE statement. This has been verified through testing on a temporary table, where the id column was added as the primary key and auto-increment values were inserted for each existing row, starting with 1.

The above is the detailed content of How to Automatically Add Auto-Increment Primary Keys to Existing Database Rows?. For more information, please follow other related articles on the PHP Chinese website!