How Do I Add Auto-Increment to Existing MySQL Table Primary Keys?

Adding Auto-Increment to Existing MySQL Table Primary Keys

After acquiring a database lacking auto-incrementers on tables with primary key IDs, you may encounter the need to convert them to auto-increment columns. You can accomplish this through the following steps:

1. Modify the Column Definition:

   Use the ALTER TABLE statement with the MODIFY COLUMN clause to redefine the primary key column as AUTO_INCREMENT. For instance, to modify the id column in the foo table:

   ALTER TABLE foo MODIFY COLUMN id INT NOT NULL AUTO_INCREMENT;

2. Verify the Effect:

   Execute SHOW CREATE TABLE foo to confirm that the modification has taken effect. The output should display the updated column definition with the AUTO_INCREMENT option.

3. Test Insertion:

   Insert a new row into the table without specifying a value for the id column:

   INSERT INTO foo () VALUES ();

   The newly inserted row will be assigned the automatically generated value.

Troubleshooting Error 150:

If you encounter a "Error on rename of ... (errorno: 150)" error during the process, it may indicate a conflict with foreign key constraints. You can refer to the following resources for assistance with diagnosing the issue:

• [What does mysql error 1025 (HY000): Error on rename of '.foo' (errorno: 150) mean?](https://stackoverflow.com/questions/11444139/what-does-mysql-error-1025-hy000-error-on-rename-of-foo-errorno-150-me)
• [http://www.simplicidade.org/notes/archives/2008/03/mysql_errno_150.html](http://www.simplicidade.org/notes/archives/2008/03/mysql_errno_150.html)

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