AUTO_INCREMENT in MySQL

What is AUTO_INCREMENT

AUTO_INCREMENT tells MySQL to automatically generate a new, unique number for a column each time you insert a row. It is almost always used on the primary key, so you never have to think up ids yourself — MySQL fills them in, one higher each time.

Creating an auto id

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50)
);

Adding AUTO_INCREMENT to the integer primary key id means MySQL will number each new row automatically, starting from 1.

Inserting rows

INSERT INTO students (name) VALUES ('Aman');
INSERT INTO students (name) VALUES ('Diya');
SELECT * FROM students;

Notice we did not give an id — MySQL assigned 1, then 2 automatically. You just insert the other columns.

Getting the last id

INSERT INTO students (name) VALUES ('Kabir');
SELECT LAST_INSERT_ID();

LAST_INSERT_ID() returns the id MySQL just generated. This is very useful when you insert a parent row and need its new id to insert related child rows.

Resetting the counter

ALTER TABLE students AUTO_INCREMENT = 100;
-- next inserted row gets id 100

You can set where the counter continues from. TRUNCATE TABLE students; also resets it back to 1, while DELETE leaves the counter where it was. You cannot set it below the highest existing id.

Useful notes

• Gaps are normal. Deleting a row does not reuse its id; the counter keeps rising.
• One per table. A table can have only one AUTO_INCREMENT column.
• Must be a key. The column should be a primary or unique key.

These behaviours keep ids unique and predictable.

Common mistakes

• Supplying the id yourself. Let MySQL generate it; just insert the other columns.
• Expecting deleted ids to be reused. They are not — gaps are fine.
• Setting AUTO_INCREMENT below the max id. MySQL ignores values that would clash.
• Using it on a non-key column. AUTO_INCREMENT needs a primary or unique key.

Summary

• AUTO_INCREMENT auto-generates unique ids for new rows.
• Add it to an integer primary key: id INT PRIMARY KEY AUTO_INCREMENT.
• Insert without the id; MySQL assigns the next number.
• LAST_INSERT_ID() returns the id just created.
• TRUNCATE resets the counter; deleted ids are not reused.

Next, add many rows at once with inserting multiple rows, or key concepts in primary key vs foreign key.