Auto Increment

Introduction

Every row usually needs a unique identifier, but manually tracking "what is the next free ID?" in your application code is tedious and error-prone, especially with multiple users inserting data at the same time.

MySQL solves this with the AUTO_INCREMENT attribute, which automatically generates the next unique number for a column every time a new row is inserted.

What is AUTO_INCREMENT?

AUTO_INCREMENT is a column attribute that tells MySQL to automatically generate a new, unique, incrementing number for that column each time a row is inserted — you never have to supply a value for it yourself.

It is almost always used on a numeric PRIMARY KEY column, since that is exactly the kind of column that needs a guaranteed-unique value for every single row.

Defining an Auto-Increment Column

You add the AUTO_INCREMENT keyword right after the column's data type.

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

Now "id" will be filled in automatically by MySQL, starting at 1 by default and increasing by 1 with every new row.

Worked Example: Multiple Inserts

Notice that the "id" column is left out of the column list entirely — MySQL generates it for us.

INSERT INTO students (name, email) VALUES ('Alex', 'alex@example.com');
INSERT INTO students (name, email) VALUES ('Sam', 'sam@example.com');
INSERT INTO students (name, email) VALUES ('Priya', 'priya@example.com');

SELECT * FROM students;

+----+-------+---------------------+
| id | name  | email               |
+----+-------+---------------------+
| 1  | Alex  | alex@example.com    |
| 2  | Sam   | sam@example.com     |
| 3  | Priya | priya@example.com   |
+----+-------+---------------------+

Each new row automatically received the next available number — 1, then 2, then 3 — without any of the INSERT statements mentioning "id" at all.

Checking the Last Inserted ID

After inserting a row, you often need to know which ID MySQL just generated — for example, to immediately insert related data in another table. The LAST_INSERT_ID() function returns exactly that, for the current connection.

INSERT INTO students (name, email) VALUES ('Rohan', 'rohan@example.com');

SELECT LAST_INSERT_ID();

+------------------+
| LAST_INSERT_ID() |
+------------------+
| 4                |
+------------------+

Why You Rarely Assign IDs Manually

You could technically still specify an id value yourself in an INSERT, and MySQL will accept it as long as it does not collide with an existing one. But doing this regularly is risky.

INSERT INTO students (id, name, email) VALUES (10, 'Zara', 'zara@example.com');
INSERT INTO students (name, email) VALUES ('Kabir', 'kabir@example.com');

SELECT * FROM students WHERE name IN ('Zara', 'Kabir');

+----+-------+---------------------+
| id | name  | email               |
+----+-------+---------------------+
| 10 | Zara  | zara@example.com    |
| 11 | Kabir | kabir@example.com   |
+----+-------+---------------------+

Notice MySQL jumped straight to 11 for the next auto-generated row, because it always continues counting from the highest value ever used, even one you supplied manually. Manually assigning IDs makes it easy to accidentally create gaps, collisions with concurrent inserts, or IDs that conflict with future auto-generated ones — so it is best left to MySQL.

Common Mistakes

Key Takeaways

Summary

AUTO_INCREMENT removes an entire class of bugs and complexity by letting MySQL generate unique row identifiers for you, instead of your application having to track and coordinate the "next" ID itself.

In this lesson, you defined an auto-incrementing primary key, inserted multiple rows without specifying an id, and checked the generated value with LAST_INSERT_ID(). Next, you'll take a deeper look at the INSERT statement itself.