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
MODIFY yourColumnName INT NOT NULL AUTO_INCREMENT;

Using PhpMyAdmin

To open PhpMyAdmin on localhost, you need to type the following URL in your browser −

localhost/phpmyadmin

The PhpMyAdmin interface appears as follows −

Above, we already have a table "AutoIncrementDemo". In that, we have a column "UserId" set as Primary key. To add auto_increment to the same column, check the A.I checkbox as shown in the interface.

After checking the A.I option, press the Save button to apply the changes.

Using SQL Commands

Let us see how to add auto_increment to MySQL database using SQL commands. First, create a table −

<?php
$connection = mysqli_connect("localhost", "username", "password", "database");

// Create table
$query = "CREATE TABLE AutoIncrementDemo (
    UserId int
)";

if (mysqli_query($connection, $query)) {
    echo "Table created successfully";
} else {
    echo "Error creating table: " . mysqli_error($connection);
}
?>

Example

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

<?php
$connection = mysqli_connect("localhost", "username", "password", "database");

// Add auto_increment to existing column
$query = "ALTER TABLE AutoIncrementDemo 
          MODIFY COLUMN UserId INT NOT NULL AUTO_INCREMENT PRIMARY KEY";

if (mysqli_query($connection, $query)) {
    echo "Auto increment added successfully";
} else {
    echo "Error: " . mysqli_error($connection);
}
?>

Check the table structure with the DESC command −

<?php
$connection = mysqli_connect("localhost", "username", "password", "database");

// Describe table structure
$query = "DESC AutoIncrementDemo";
$result = mysqli_query($connection, $query);

while ($row = mysqli_fetch_assoc($result)) {
    print_r($row);
}
?>

The output shows the table structure −

+--------+---------+------+-----+---------+----------------+
| Field  | Type    | Null | Key | Default | Extra          |
+--------+---------+------+-----+---------+----------------+
| UserId | int(11) | NO   | PRI | NULL    | auto_increment |
+--------+---------+------+-----+---------+----------------+

The Extra column now shows "auto_increment", confirming that the auto-increment property has been successfully added to the UserId column. This ensures that each new record will automatically receive a unique, incrementing value for the UserId field.