How to add auto-increment to column in MySQL database using PhpMyAdmin?

You can add auto_increment to a column in MySQL database with the help of ALTER command.

The syntax is as follows −

ALTER TABLE yourTableName

To open PhpMyAdmin on localhost, you need to type the following on localhost and press enter −


The screenshot is as follows −

Above, we already have a table “AutoIncrementDemo”. In that, we have a column “UserId” set as Primary key. Let’s say we need to add auto_increment to the same column.

For auto_increment, check the A.I as shown above. The same is marked below as well −

After that press the Save button.

Let us also see how to add auto_increment to MySQL database.

Here is the query to add an auto_increment column in MySQL database. First, create a table. The query to create a table is as follows −

mysql> create table AutoIncrementDemo
    -> (
    -> UserId int
    -> );
Query OK, 0 rows affected (0.45 sec)


Now add an auto_increment column to MySQL database. The query is as follows −

mysql> alter table AutoIncrementDemo
    -> modify column UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY;
Query OK, 0 rows affected (1.10 sec)
Records: 0 Duplicates: 0 Warnings: 0

Check the description of the table with the help of the DESC command. The query is as follows −

mysql> desc AutoIncrementDemo;


| Field  |    Type | Null | Key | Default |          Extra |
| UserId | int(11) |   NO | PRI |    NULL | auto_increment |
1 row in set (0.04 sec)

Updated on: 26-Jun-2020

8K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started