MySQL set auto increment in phpMyAdmin

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.

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.

Permalink

Posted by ESV