SQL query to convert all primary keys to AUTO_INCREMENT?

Question

Is there any SQL query which we run and it will update every table's primary ID to AUTO_INCREMENT (checked) ?

More than 300 or perhaps 600 didn't count them yet. But its taking too long to open each table and update it. — Raheel Ayub, Mar 06 '13 at 17:54
You can write a script. The metadata is stored in `information_schema.TABLES` and `information_schema.COLUMNS`. The PK is stored as the string `'PRI'` in `COLUMNS.COLUMN_KEY`. — Michael Berkowski, Mar 06 '13 at 17:57
You would need to loop over all tables, find the primary key column and issue an `ALTER TABLE` statement to add it. You would run into potential key collisions though. — Michael Berkowski, Mar 06 '13 at 17:58
@Perception I don't know what happens if you add `AUTO_INCREMENT` to an existing column that already holds data, without also specifying the AI starting value. I'm not sure if MySQL will automatically start after the existing MAX() for that column or attempt to start from 1. It could be avoided by choosing a starting AI value like ten million, beyond the rowcount of any of the tables. — Michael Berkowski, Mar 06 '13 at 18:06
@MichaelBerkowski - it bumps it up to the maximum value currently in the column + 1. For InnoDB anyways. You are 100% correct that the OP will need to right a script btw, especially since he will need to drop the primary key in order to redefine it with the auto_increment. — Perception, Mar 06 '13 at 18:11

score 2 · Answer 1 · edited May 23 '17 at 10:25

This is almost not a real question, but I am feeling in a good mood today and I will give you the resources you need, from there is open to you to understand how to handle those resources in order to accomplish your goal.

First, list all tables in your database:

list all tables in a database with MySQLi

There are many ways.

SHOW TABLES Is the most simple SQL statement for doing that. You can also take a look at INFORATION_SCHEMA.TABLES if you want to have more details or do some filtering or such.

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA LIKE 'your_database';

Then, iterate through your results, then for every iteration you will need to find the primary key for that table:

Get primary key of table?
A better way is to use SHOW KEYS since you don't always have access to information_schema. The following works:
```
SHOW KEYS FROM table WHERE Key_name = 'PRIMARY'
Column_name will contain the name of the primary key.
```

Now with the column name of the given table, modify the column to be auto increment with that max value.

Mysql - Alter a column to be AUTO_INCREMENT

alter table document modify column document_id int(4) auto_increment

Finally, get the max() value of the primary_key column and save that number in order that you can set the auto increment option of this table with this value so, the auto increment behavior will start in the right position.

SQL query to convert all primary keys to AUTO_INCREMENT?

1 Answers1