How to set MySQL auto increment in phpMyAdmin

A common practice in database design is to set primary keys (PKs) with auto increment enabled. This way, you don't have to worry about specifying a new unique primary key value each time you insert a new record in such table.

While phpMyAdmin is a very powerful and easy to use MySQL database management tool, "where could I set auto increment in phpMyAdmin" is still a frequent question. And here is the solution.

In the latest phpMyAdmin versions there is a new A_I Checkbox. Mark this option as enabled when creating or editing your primary key table column and that numeric field will automatically increment its value each time a new record is inserted.

Setting auto increment in phpMyAdmin

You can check that the auto increment property was successfully setup in the EXTRA column of the table column properties (after selecting a table, inside the structure tab). If the auto_increment text appears here, the configuration was successful.

In previous phpMyAdmin versions, auto_increment was an additional option inside the dropdown menu of the EXTRA category (the last column in the field creation menu). To access the "edit table field menu" you can click the pencil icon in the desired table field row, inside the Structure tab.

Anyway, you can always run an SQL command to update the auto increment status of the desired column by selecting the SQL tab and writing an SQL query like this one:

ALTER TABLE `table_name`
    CHANGE `pk_column_name` `pk_column_name`
           INT(key_length) NOT NULL AUTO_INCREMENT

Just replace "table_name" by the name of the current table being edited, "pk_column_name" by the column name of your primary key column and "key_length" by your integer primary key length (the default int length is 11).

You should also make sure that the auto incrementing field is in fact the primary key of the current table. You can reset the table primary key by clicking on the table key icon of the desired field row.

Take also into account that only one auto incrementing field may be specified in each MySQL table. Nevertheless, auto incrementing only makes sense when used with numeric primary keys, and only a primary key field may be specified for each MySQL table as well.

Finally, if you want to change the current Auto Increment Value (i.e.: to make your auto increment field start from a specific numeric value) just select the phpMyAdmin Operations tab, type in the AUTO_INCREMENT field the new starting value of your auto incrementing field, and you are ready to go.

9 comments:

Anonymous said...

Thanks for this post - I have been trying to find usefu help on the new PHPMyAdmin app for ages, and this answered by question perfectly.

ESV said...

The options to edit auto increment have changed in the latest phpMyAdmin versions, and I also found that there was little documentation about this topic. That's why I'm writing phpMyAdmin tips as I'm finding them.
I'm glad to know it was helpful! :-)

Unknown said...

Dude, thanks for this post. I was trying to find the auto increment option and it did not occur to me to tick "AI". It was different int he older versions of phpmyadmin.

ESV said...

Same happened to me: I found the new A_I checkbox rather confusing and I was wondering where did they put the Auto Increment option.

Willow Deen said...

I love your blog--has been very helpful. Just one thing--could you put a "search" on it?

Willow Deen said...

Love your blog. Just needs one thing--a search function.

ESV said...

Hi, Willow.

We're glad that you love our blog. Thank you!

There's already a search field on the top-left corner which you can use to easily browse the blog. In fact, all blogs from Blogger include such feature on their top Blogger bar.

xian said...

Columns AUto Increment hide.
thanks you

Lisa Ariel said...

Thanks for this post.

Just wondering - my AUTO-INCREMENT is inexplicably going up by 10 rather than 1 with each new row.

Any ideas on how to make it increment by 1 instead of 10?
Thanks very much