How to Add an Auto Increment Column in MySQL

In today’s post, we’ll learn how to add an auto-increment column in MySQL.

Add an Auto Increment Column in MySQL

When building a table, we may not have a unique identity within the database, which makes selecting a primary key problematic. To tackle such a problem, we must manually assign unique keys to each record, which is typically time-consuming.

As a result, we may use the Auto Increment feature, which creates a numerical primary key value for each new record added.

The AUTO_INCREMENT keyword is used in MySQL for the auto-increment capability. AUTO_INCREMENT begins with 1 and rises by 1 by default.

To further understand the previous concept, consider the following example.

Example 1: Add a column to a new table.

CREATE TABLE Employees(
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    first_Name VARCHAR(255),
    last_Name VARCHAR(255)
);

In the above example, we have built an Employees table with the fields employee_id, first_Name, and last_Name. This will auto-generate the employee_id using AUTO_INCREMENT and make it the database’s primary key.

Example 2: Modify the column of an existing table.

ALTER TABLE `Employees`
    MODIFY COLUMN `employee_id` INT NOT NULL AUTO_INCREMENT;

We already have an Employees table with the fields employee_id, first_Name, and last_Name in the prior example. We will use AUTO_INCREMENT to produce the employee_id by altering the existing attribute of the employee_id column.

Since it will be inserted automatically, we won’t need to specify the value of employee_id when adding a new record to the Employees table. The initial record’s key will be 1, and the key for each succeeding record will rise by 1.

INSERT INTO Employees(first_Name, last_Name)
VALUES
  ('John', 'Doe'),
  ('Smith', 'Will'),
  ('Tony', 'Stark');

We may use the ALTER TABLE command as shown below to modify the default beginning value:

ALTER TABLE Employees AUTO_INCREMENT = new_value;

We can set a new_value as the default beginning value like this:

ALTER TABLE Employees AUTO_INCREMENT = 1001;

In this case, the initial value we want to utilize is new_value. We give a new interval value to the MySQL server variable AUTO_INCREMENT to modify the AUTO_INCREMENT interval value to a number other than 1.

We have set this number to 1001 for our example.

Run the above code line in any browser compatible with MySQL. It will display the following outcome:

After modifying the default beginning value: