AUTO_INCREMENT behavior in MySQL - Step-by-Step Execution

Concept Flow - AUTO_INCREMENT behavior

Create Table with AUTO_INCREMENT

↓

Insert Row without ID

↓

MySQL assigns next AUTO_INCREMENT value

↓

Row inserted with assigned ID

↓

AUTO_INCREMENT counter increments

↓

Next insert uses updated AUTO_INCREMENT value

When you insert a row without specifying the AUTO_INCREMENT column, MySQL automatically assigns the next number, then increases the counter for the next insert.

Execution Sample

MySQL

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

INSERT INTO users (name) VALUES ('Alice');
INSERT INTO users (name) VALUES ('Bob');

Creates a table with an AUTO_INCREMENT id, then inserts two rows without specifying id, so MySQL assigns ids automatically.

Execution Table

Step	Action	AUTO_INCREMENT value before	Inserted id	AUTO_INCREMENT value after	Notes
1	Create table with AUTO_INCREMENT	1 (default start)	N/A	1	Table created, counter starts at 1
2	Insert 'Alice' without id	1	1	2	MySQL assigns id=1, counter increments to 2
3	Insert 'Bob' without id	2	2	3	MySQL assigns id=2, counter increments to 3
4	Insert with explicit id=10	3	10	11	Explicit id=10 inserted, counter jumps to 11
5	Insert without id again	11	11	12	MySQL assigns id=11, counter increments to 12
6	Insert with explicit id=5 (lower than counter)	12	5	12	Explicit id=5 inserted, counter stays at 12
7	Insert without id again	12	12	13	MySQL assigns id=12, counter increments to 13
8	Stop				No more inserts

💡 Execution stops after last insert; AUTO_INCREMENT counter updates after each insert.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	After Step 6	After Step 7	Final
AUTO_INCREMENT counter	1	2	3	11	12	12	13	13

Key Moments - 3 Insights

Why does the AUTO_INCREMENT counter jump to 11 after inserting a row with explicit id=10?

Because MySQL sets the AUTO_INCREMENT counter to one more than the highest explicit id inserted, as shown in step 4 of the execution_table.

What happens if you insert a row with an explicit id lower than the current AUTO_INCREMENT counter?

The counter does not change and stays at the higher value, as shown in step 6 where id=5 is inserted but counter remains 12.

How does MySQL assign ids when you do not specify the id column?

It uses the current AUTO_INCREMENT counter value, inserts that as the id, then increments the counter by 1, as shown in steps 2, 3, 5, and 7.

Visual Quiz - 3 Questions

Look at the execution_table, what is the AUTO_INCREMENT counter value after inserting 'Bob'?

A 1

B 2

C 3

D 4

💡 Hint

Check the 'AUTO_INCREMENT value after' column in row 3 of the execution_table.

At which step does the AUTO_INCREMENT counter jump due to an explicit id insertion?

A Step 3

B Step 4

C Step 6

D Step 7

💡 Hint

Look for the step where the inserted id is 10 and the counter changes from 3 to 11.

If you insert a row without specifying id after step 7, what id will MySQL assign?

A 13

B 14

C 12

D 11

💡 Hint

Check the AUTO_INCREMENT counter after step 7 in variable_tracker.

Concept Snapshot

AUTO_INCREMENT behavior in MySQL:
- Define a column with AUTO_INCREMENT to auto-generate unique IDs.
- When inserting without specifying the ID, MySQL assigns the current counter value.
- The counter starts at 1 by default and increments after each insert.
- Inserting an explicit ID higher than the counter moves the counter up.
- Inserting an explicit ID lower than the counter does not change it.

Full Transcript

This visual execution trace shows how MySQL handles AUTO_INCREMENT columns. When a table is created with an AUTO_INCREMENT column, the counter starts at 1. Each insert without specifying the ID uses the current counter value and then increments it. If you insert a row with an explicit ID higher than the current counter, MySQL updates the counter to one more than that ID. If the explicit ID is lower, the counter remains unchanged. This ensures unique, increasing IDs for new rows.

Practice

1. What does the AUTO_INCREMENT attribute do in a MySQL table?

easy

A. Automatically generates a unique number for each new row

B. Allows duplicate values in a column

C. Automatically deletes rows with duplicate values

D. Encrypts the column data

Solution

1. Step 1: Understand the purpose of AUTO_INCREMENT

   AUTO_INCREMENT is used to create unique numbers automatically for new rows in a table, usually for primary keys.

2. Step 2: Compare options with the purpose

   Only Automatically generates a unique number for each new row correctly describes this behavior; others describe unrelated features.

3. Final Answer:

   Automatically generates a unique number for each new row -> Option A

4. Quick Check:

   AUTO_INCREMENT = unique number generation [OK]

Hint: AUTO_INCREMENT means unique numbers auto-made for new rows [OK]

Common Mistakes:

• Thinking AUTO_INCREMENT allows duplicates
• Confusing AUTO_INCREMENT with encryption
• Assuming AUTO_INCREMENT deletes rows

2. Which of the following is the correct syntax to create a table with an AUTO_INCREMENT column in MySQL?

easy

A. CREATE TABLE users (id INT AUTO_INCREMENT, name VARCHAR(50), PRIMARY KEY(name));

B. CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));

C. CREATE TABLE users (id INT, name VARCHAR(50) AUTO_INCREMENT, PRIMARY KEY(id));

D. CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));

Solution

1. Step 1: Review correct AUTO_INCREMENT syntax

   The AUTO_INCREMENT attribute must be on an integer column and usually combined with PRIMARY KEY.

2. Step 2: Check each option

   CREATE TABLE users (id INT AUTO_INCREMENT, name VARCHAR(50), PRIMARY KEY(name)); misses PRIMARY KEY on the AUTO_INCREMENT column. CREATE TABLE users (id INT, name VARCHAR(50) AUTO_INCREMENT, PRIMARY KEY(id)); wrongly applies AUTO_INCREMENT to a VARCHAR column. CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50)); correctly combines AUTO_INCREMENT and PRIMARY KEY on an INT column. CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50)); lacks AUTO_INCREMENT.

3. Final Answer:

   CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50)); -> Option B

4. Quick Check:

   Correct syntax = CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50)); [OK]

Hint: AUTO_INCREMENT must be on INT and PRIMARY KEY column [OK]

Common Mistakes:

• Putting AUTO_INCREMENT on non-integer columns
• Forgetting PRIMARY KEY on AUTO_INCREMENT column
• Misplacing AUTO_INCREMENT after other column attributes

3. Given the table products created as:

CREATE TABLE products (product_id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));

and these inserts:

INSERT INTO products (name) VALUES ('Pen');
INSERT INTO products (name) VALUES ('Pencil');
INSERT INTO products (name) VALUES ('Eraser');

What will be the product_id of the 'Eraser' row?

medium

A. 1

B. 2

C. 3

D. NULL

Solution

1. Step 1: Understand AUTO_INCREMENT numbering

   Each insert without specifying product_id gets the next number starting from 1.

2. Step 2: Count inserts before 'Eraser'

   'Pen' gets 1, 'Pencil' gets 2, so 'Eraser' gets 3.

3. Final Answer:

   3 -> Option C

4. Quick Check:

   Third insert gets product_id 3 [OK]

Hint: AUTO_INCREMENT counts inserts starting at 1 by default [OK]

Common Mistakes:

• Assuming IDs start at 0
• Thinking IDs reset after each insert
• Expecting NULL for AUTO_INCREMENT column

4. Consider this table creation:

CREATE TABLE orders (
  order_id INT AUTO_INCREMENT,
  product VARCHAR(50),
  PRIMARY KEY (order_id)
);

Which of the following statements will cause an error when inserting data?

medium

A. INSERT INTO orders (order_id, product) VALUES ('abc', 'Pencil');

B. INSERT INTO orders (order_id, product) VALUES (NULL, 'Pen');

C. INSERT INTO orders (order_id, product) VALUES (5, 'Notebook');

D. INSERT INTO orders (product) VALUES ('Book');

Solution

1. Step 1: Analyze each insert statement

   INSERT INTO orders (product) VALUES ('Book'); inserts without order_id, so AUTO_INCREMENT assigns a number. INSERT INTO orders (order_id, product) VALUES (NULL, 'Pen'); inserts NULL for order_id, which is allowed and triggers AUTO_INCREMENT. INSERT INTO orders (order_id, product) VALUES (5, 'Notebook'); inserts a specific valid integer 5, allowed if unique. INSERT INTO orders (order_id, product) VALUES ('abc', 'Pencil'); tries to insert a string 'abc' into an INT column, causing a type error.

2. Step 2: Identify the error-causing statement

   Only INSERT INTO orders (order_id, product) VALUES ('abc', 'Pencil'); causes a type mismatch error.

3. Final Answer:

   INSERT INTO orders (order_id, product) VALUES ('abc', 'Pencil'); -> Option A

4. Quick Check:

   Non-integer in INT column causes error [OK]

Hint: Only integers allowed in AUTO_INCREMENT column [OK]

Common Mistakes:

• Thinking NULL is invalid for AUTO_INCREMENT
• Assuming manual insert of valid integer always errors
• Ignoring data type mismatch errors

5. A table employees has an AUTO_INCREMENT primary key emp_id. After inserting 10 rows, you delete the row with emp_id = 10. What happens when you insert a new row without specifying emp_id?

hard

A. The new row gets emp_id = 10 (reuses deleted ID)

B. The emp_id is set to NULL

C. The insert fails due to duplicate key

D. The new row gets emp_id = 11 (continues incrementing)

Solution

1. Step 1: Understand AUTO_INCREMENT behavior on deletions

   Deleting a row does not reset or reuse the AUTO_INCREMENT counter.

2. Step 2: Determine next AUTO_INCREMENT value

   After 10 inserts, the next value is 11, even if 10 was deleted.

3. Final Answer:

   The new row gets emp_id = 11 (continues incrementing) -> Option D

4. Quick Check:

   Deleted IDs are not reused by AUTO_INCREMENT [OK]

Hint: AUTO_INCREMENT never reuses deleted IDs, always increments [OK]

Common Mistakes:

• Assuming AUTO_INCREMENT reuses deleted IDs
• Expecting insert failure due to missing IDs
• Thinking AUTO_INCREMENT resets automatically