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.
![How to set an auto incrementing primary key in MySQL with phpMyAdmin Setting auto increment in phpMyAdmin](http://eserrano.com/treble-click/images/phpMyAdmin-auto-increment.gif)
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:
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:
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.
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! :-)
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.
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.
I love your blog--has been very helpful. Just one thing--could you put a "search" on it?
Love your blog. Just needs one thing--a search function.
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.
Columns AUto Increment hide.
thanks you
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
Post a Comment