MySQL AUTO_INCREMENT

AUTO_INCREMENT is a MySQL feature that automatically generates a unique number for a column each time a new row is inserted. It is most commonly used with the PRIMARY KEY column so that IDs are assigned automatically without manual input.

Why Use AUTO_INCREMENT?

Manually tracking and assigning unique IDs for every new record is time-consuming and error-prone. AUTO_INCREMENT removes this burden by letting MySQL handle sequential numbering automatically.

Simple Analogy

Think of a token number system at a government office. Each new visitor gets the next number automatically — no one needs to decide what the number should be. AUTO_INCREMENT works exactly the same way for database rows.

Syntax

CREATE TABLE table_name (
    column_name INT AUTO_INCREMENT PRIMARY KEY,
    other_column datatype
);

Example: Students Table

CREATE TABLE students (
    student_id INT AUTO_INCREMENT PRIMARY KEY,
    student_name VARCHAR(100) NOT NULL,
    class VARCHAR(10)
);

Inserting Without Specifying ID

INSERT INTO students (student_name, class) VALUES ('Riya Sharma', '9A');
INSERT INTO students (student_name, class) VALUES ('Anil Joshi', '10B');
INSERT INTO students (student_name, class) VALUES ('Meera Pillai', '8C');

Viewing the Result

SELECT * FROM students;

student_id | student_name  | class
-----------+---------------+------
1          | Riya Sharma   | 9A
2          | Anil Joshi    | 10B
3          | Meera Pillai  | 8C

MySQL assigned IDs 1, 2, and 3 automatically.

Changing the Starting Value

By default, AUTO_INCREMENT starts at 1. This can be changed at table creation or later.

Start at a Custom Value During Creation

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT PRIMARY KEY,
    item_name VARCHAR(100)
) AUTO_INCREMENT = 1000;

The first order ID will be 1000, then 1001, 1002, and so on.

Change Start Value on Existing Table

ALTER TABLE orders AUTO_INCREMENT = 5000;

What Happens After a Row is Deleted?

If a row is deleted, the auto-increment counter does not reset. The next insert will continue from the last highest value.

-- If IDs 1, 2, 3 exist and row with ID 3 is deleted:
DELETE FROM students WHERE student_id = 3;

-- Next insert gets ID 4, not 3
INSERT INTO students (student_name, class) VALUES ('Kiran Das', '7A');

Getting the Last AUTO_INCREMENT Value

After an insert, the last generated ID can be retrieved using:

SELECT LAST_INSERT_ID();

This is useful in application code when the newly created ID is needed immediately after insertion.

AUTO_INCREMENT Rules

• Only one AUTO_INCREMENT column is allowed per table.
• The column must be indexed (usually as the primary key).
• The column must be of an integer data type (INT, BIGINT, etc.).
• The value is never reused, even if the row is deleted.

Viewing AUTO_INCREMENT Value

SHOW TABLE STATUS LIKE 'students';

The Auto_increment column shows the next value that will be assigned.

Key Points

• AUTO_INCREMENT automatically assigns a unique sequential number to a column.
• It is most commonly used with the primary key column.
• The default starting value is 1, and it increments by 1 for each new row.
• The starting value can be customized using AUTO_INCREMENT = value.
• LAST_INSERT_ID() returns the most recently generated auto-increment value.