JIYIK CN >

In this article, we will learn how to add an auto-increment column in MySQL.

Adding an auto-increment column in MySQL

When creating a table, we may not have a unique identifier in the database, which brings problems to choosing the primary key. To solve such problems, we have to manually assign a unique key to each record, which is usually time-consuming.

Therefore, we might use the auto-increment feature, which creates a numeric primary key value for each new record added.

AUTO_INCREMENTKeyword used for the auto-increment feature in MySQL. AUTO_INCREMENTStarts at 1 and increases by 1 by default.

To further understand the previous concepts, 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 created an Employees table with the fields employee_id, first_Name and last_Name. This will automatically generate employee_id using AUTO_INCREMENT and make it the primary key of the database.

Example 2: Modify the columns of an existing table.

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

In the previous example, we already have an Employees table with fields employee_id, first_Name, and last_Name. We will use AUTO_INCREMENT to generate employee_id by changing the existing properties of the employee_id column.

Because it is automatically inserted, we do not need to specify the value of employee_id when adding a new record to the Employees table. The key of the initial record will be 1, and the key of each subsequent record will increase by 1.

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

We can modify the default starting value using the ALTER TABLE command as shown below:

ALTER TABLE Employees AUTO_INCREMENT = new_value;

We can set new_value to the default starting value like this:

ALTER TABLE Employees AUTO_INCREMENT = 1001;

In this case, the initial value we want to use is new_value. We give the MySQL server variable AUTO_INCREMENTa new interval value by AUTO_INCREMENTchanging the interval value to a number other than 1.

For our example, we have set this number to 1001.

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

After modifying the default starting value: