Auto-generated ids with AUTO_INCREMENT
35 min · Beginner
Need help? Ask a tutor on WhatsApp (opens in a new tab)Every order, ticket and student needs a number that no other row has. With AUTO_INCREMENT, MySQL hands out that number itself, 1, 2, 3 and so on, so two people booking at the same second can never get the same id.
AUTO_INCREMENT primary keys
AUTO_INCREMENT on an integer key column makes MySQL fill in the next number whenever an insert leaves that column out. Each number is handed out exactly once.
AUTO_INCREMENT on an integer column makes MySQL fill in the next number whenever an insert leaves that column out. It is almost always used on the table's primary key: the column whose value identifies one row and is never repeated.
Example 1 · A tickets table with an auto id
CREATE TABLE irctc_tickets ( ticket_id INT AUTO_INCREMENT PRIMARY KEY, passenger VARCHAR(40), train_no INT);
INSERT INTO irctc_tickets (passenger, train_no) VALUES ('Asha Rao', 12627);INSERT INTO irctc_tickets (passenger, train_no) VALUES ('Ravi Kumar', 12951), ('Meena Iyer', 12627);INSERT INTO irctc_tickets (ticket_id, passenger, train_no) VALUES (NULL, 'Kabir Singh', 22691);
SELECT * FROM irctc_tickets ORDER BY ticket_id;DESCRIBE irctc_tickets;
CREATE TABLE bad_ids ( id INT AUTO_INCREMENT, name VARCHAR(40));+-----------+-------------+----------+ | ticket_id | passenger | train_no | +-----------+-------------+----------+ | 1 | Asha Rao | 12627 | | 2 | Ravi Kumar | 12951 | | 3 | Meena Iyer | 12627 | | 4 | Kabir Singh | 22691 | +-----------+-------------+----------+ +-----------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-----------+-------------+------+-----+---------+----------------+ | ticket_id | int | NO | PRI | NULL | auto_increment | | passenger | varchar(40) | YES | | NULL | | | train_no | int | YES | | NULL | | +-----------+-------------+------+-----+---------+----------------+ ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
What this code does
- No insert gave a
ticket_id, exceptNULL, which also means "next number". MySQL handed out 1 to 4 in order. DESCRIBEshows the key asPRIandauto_incrementunder Extra. The primary key also becameNOT NULL(Null isNO) by itself.bad_idsfails with error 1075: an auto column that is not a key is not allowed. AddingPRIMARY KEYfixes it.
The Keys & Constraints module covers primary keys in depth; here, PRIMARY KEY simply means "unique id of the row".
Why not let the app pick the number? An app might read the biggest id and add 1, but two users doing that at the same moment get the same number. MySQL does this even with thousands of inserts per second, and the app does not need to think about it. The rules:
- The column must be an integer type and must be a key, usually the
PRIMARY KEY; otherwise you get error 1075. A table can have only one auto column. - Choose
INTorBIGINT. When the type runs out (127 forTINYINT), the next insert fails with a duplicate entry error. - Inserting
NULLor0into the column, or leaving it out, all mean "give me the next number".
Fix the error
Fix the auto column
Read the real error, then pick the line that fixes it.
The table will not be created.
Error
ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key
LAST_INSERT_ID() after an insert
LAST_INSERT_ID() returns the id MySQL generated for the last insert on your own connection. Other users never change it, so it is safe to use for the rows that follow.
LAST_INSERT_ID() returns the id MySQL generated for the last insert on your own connection. You need it whenever a new row is followed by rows that point to it: insert an order, then its order items with that order's id.
Example 2 · A new ShopLite order and its items
INSERT INTO orders (customer_id, order_date, status)VALUES (3, '2025-06-05', 'PLACED');
SELECT LAST_INSERT_ID();
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES (LAST_INSERT_ID(), 9, 1, 499.00), (LAST_INSERT_ID(), 11, 2, 399.00);
SELECT * FROM order_items WHERE order_id = 16 ORDER BY product_id;+------------------+ | LAST_INSERT_ID() | +------------------+ | 16 | +------------------+ +----------+------------+----------+------------+ | order_id | product_id | quantity | unit_price | +----------+------------+----------+------------+ | 16 | 9 | 1 | 499.00 | | 16 | 11 | 2 | 399.00 | +----------+------------+----------+------------+
What this code does
- The practice
orderstable already has orders 1 to 15, so Meena's new order became 16 andLAST_INSERT_ID()returns 16. - The order items use
LAST_INSERT_ID()directly as theirorder_id, so they link to the right order even if other customers are ordering at the same time.WHERE order_id = 16keeps only this order's rows; the Querying module coversWHERE. - Inserting into
order_itemsdoes not changeLAST_INSERT_ID(), because that table has no auto column.
Example 3 · Several rows at once, and ids you give yourself
CREATE TABLE ipl_tickets ( ticket_id INT AUTO_INCREMENT PRIMARY KEY, stand VARCHAR(20));
INSERT INTO ipl_tickets (stand) VALUES ('North'), ('South'), ('East');SELECT LAST_INSERT_ID();
INSERT INTO ipl_tickets (ticket_id, stand) VALUES (500, 'VIP');SELECT LAST_INSERT_ID();
SELECT * FROM ipl_tickets ORDER BY ticket_id;+------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ +------------------+ | LAST_INSERT_ID() | +------------------+ | 1 | +------------------+ +-----------+-------+ | ticket_id | stand | +-----------+-------+ | 1 | North | | 2 | South | | 3 | East | | 500 | VIP | +-----------+-------+
What this code does
- One INSERT added tickets 1, 2 and 3, and
LAST_INSERT_ID()returns the first of them: 1, not 3. - The VIP ticket gave its own id, 500, so
LAST_INSERT_ID()stayed at 1. The next generated id will be 501.
Why not SELECT MAX(order_id)? On a busy store, another customer may place an order between your insert and your query, and MAX would give you their id.
Two details:
- After one
INSERTthat adds several rows, it returns the id of the first of them. - An insert that gives the id itself (such as
order_id = 500) does not change it.
SELECT LAST_INSERT_ID() by hand: the database driver of most languages returns the generated id right after the insert. Underneath, it is the same value.Ids are never reused and may have gaps
An auto-increment counter only moves forward. Delete order 5 and the next order is still 6, not 5. Reusing 5 would be dangerous: an old invoice, email or log line that says "order 5" would suddenly point to someone else's order.
Example 4 · Deleted ids leave gaps
CREATE TABLE hospital_tokens ( token_no INT AUTO_INCREMENT PRIMARY KEY, patient VARCHAR(40));
INSERT INTO hospital_tokens (patient) VALUES ('Kavya'), ('Imran'), ('Sneha');DELETE FROM hospital_tokens WHERE token_no = 3;INSERT INTO hospital_tokens (patient) VALUES ('Rahul');SELECT * FROM hospital_tokens ORDER BY token_no;
INSERT INTO hospital_tokens (token_no, patient) VALUES (100, 'Priya');INSERT INTO hospital_tokens (patient) VALUES ('Divya');SELECT * FROM hospital_tokens ORDER BY token_no;+----------+---------+ | token_no | patient | +----------+---------+ | 1 | Kavya | | 2 | Imran | | 4 | Rahul | +----------+---------+ +----------+---------+ | token_no | patient | +----------+---------+ | 1 | Kavya | | 2 | Imran | | 4 | Rahul | | 100 | Priya | | 101 | Divya | +----------+---------+
What this code does
DELETE ... WHERE token_no = 3removes Sneha's row (the Changing Data module covers DELETE). Rahul gets 4, not 3: numbers are never reused.- Inserting token 100 by hand moves the counter past it, so Divya gets 101. Tokens 5 to 99 will never be used.
Example 5 · A failed insert also uses up a number
CREATE TABLE app_users ( user_id INT AUTO_INCREMENT PRIMARY KEY, email VARCHAR(100) UNIQUE);
INSERT INTO app_users (email) VALUES ('asha@example.com'), ('ravi@example.com');INSERT INTO app_users (email) VALUES ('asha@example.com');INSERT INTO app_users (email) VALUES ('meena@example.com');SELECT * FROM app_users ORDER BY user_id;ERROR 1062 (23000): Duplicate entry 'asha@example.com' for key 'app_users.email' +---------+-------------------+ | user_id | email | +---------+-------------------+ | 1 | asha@example.com | | 2 | ravi@example.com | | 4 | meena@example.com | +---------+-------------------+
What this code does
- Run the statements one at a time.
UNIQUEmeans no two rows may share an email (Keys & Constraints covers it), so the second Asha insert fails with error 1062. - MySQL had already taken number 3 for that insert, so Meena gets 4. This is the default behaviour of MySQL 8.0 and later; the exact gaps after failed or parallel inserts can differ, which is why this is a sample.
So gaps are normal. They come from deleted rows, from inserts that failed (the number was taken before the insert was refused), from transactions that were cancelled, and from inserting a bigger id by hand: insert id 100 and the counter jumps to 101.
Never use an auto id as a count of rows or as a gap-free bill number. If the law needs gap-free invoice numbers, the app makes them in a separate, carefully controlled step; the id is only an identity.
Predict the output
Predict the token
The table hospital_tokens is empty and token_no is AUTO_INCREMENT. Which token does Rahul get?
Starting value and resetting in practice tables
TRUNCATE TABLE removes every row in one step and resets the AUTO_INCREMENT counter to 1. DELETE FROM removes the rows but keeps the counter. Use TRUNCATE only on test data.
AUTO_INCREMENT = n sets the next number to hand out. Put it after the closing bracket of CREATE TABLE to start a table at 1001 (ticket numbers that look like real ones), or run ALTER TABLE t AUTO_INCREMENT = n later.
Example 6 · Start at 1001, then reset with TRUNCATE
CREATE TABLE cinema_bookings ( booking_id INT AUTO_INCREMENT PRIMARY KEY, seat VARCHAR(4)) AUTO_INCREMENT = 1001;
INSERT INTO cinema_bookings (seat) VALUES ('F7'), ('F8');SELECT * FROM cinema_bookings ORDER BY booking_id;
DELETE FROM cinema_bookings;INSERT INTO cinema_bookings (seat) VALUES ('A1');SELECT * FROM cinema_bookings;
TRUNCATE TABLE cinema_bookings;INSERT INTO cinema_bookings (seat) VALUES ('B2');SELECT * FROM cinema_bookings;+------------+------+ | booking_id | seat | +------------+------+ | 1001 | F7 | | 1002 | F8 | +------------+------+ +------------+------+ | booking_id | seat | +------------+------+ | 1003 | A1 | +------------+------+ +------------+------+ | booking_id | seat | +------------+------+ | 1 | B2 | +------------+------+
What this code does
AUTO_INCREMENT = 1001after the bracket makes the first booking 1001.DELETE FROM cinema_bookings(noWHERE) removes every row but keeps the counter, so the next booking is 1003.TRUNCATE TABLEempties the table and resets the counter to 1. That is fine for practice tables, never for real ones.
MySQL never sets it below the current largest id + 1, so you cannot use it to create duplicates.
To start again from 1 in a practice table, use TRUNCATE TABLE t, or drop and recreate the table, as the practice scripts do. Only reset counters on test data. On a real table, an old id that comes back would mix up history, which is exactly what the counter protects against.
Next step
Check your understanding
10 quick questions