AUTO_INCREMENT behavior in SQL - Step-by-Step Execution

Concept Flow - AUTO_INCREMENT behavior

Create Table with AUTO_INCREMENT

↓

Insert Row without ID

↓

Database assigns next AUTO_INCREMENT value

↓

Row inserted with assigned ID

↓

Next insert uses incremented ID

↓

Repeat or Stop

When you insert a row without specifying the ID, the database automatically assigns the next number in sequence to the AUTO_INCREMENT column.

Execution Sample

SQL

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 and inserts two rows, letting the database assign IDs automatically.

Execution Table

Step	Action	AUTO_INCREMENT Value Before	Inserted ID	Resulting Table Rows
1	Create table with AUTO_INCREMENT	1 (start)	N/A	Table created, no rows
2	Insert 'Alice' without ID	1	1	[{id:1, name:'Alice'}]
3	Insert 'Bob' without ID	2	2	[{id:1, name:'Alice'}, {id:2, name:'Bob'}]
4	Insert with explicit ID 5	3	5	[{id:1, name:'Alice'}, {id:2, name:'Bob'}, {id:5, name:'Explicit'}]
5	Insert 'Carol' without ID	6	6	[{id:1, name:'Alice'}, {id:2, name:'Bob'}, {id:5, name:'Explicit'}, {id:6, name:'Carol'}]
6	Stop	-	-	No more inserts

💡 Execution stops after no more inserts; AUTO_INCREMENT increments after each insert, skipping used explicit IDs.

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	After Step 5	Final
AUTO_INCREMENT Value	1	2	3	6	7	7
Table Rows Count	0	1	2	3	4	4

Key Moments - 2 Insights

Why does the AUTO_INCREMENT value jump from 3 to 6 after inserting a row with explicit ID 5?

Because inserting a row with an explicit ID higher than the current AUTO_INCREMENT value causes the AUTO_INCREMENT counter to update to one more than that explicit ID, as shown in step 4 and 5 of the execution_table.

What happens if you insert a row without specifying the ID?

The database assigns the current AUTO_INCREMENT value as the ID, then increments the counter by 1, as seen in steps 2, 3, and 5.

Visual Quiz - 3 Questions

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

A 1

B 2

C 3

D 5

💡 Hint

Check the 'AUTO_INCREMENT Value Before' column at step 3 in the execution_table.

At which step does the AUTO_INCREMENT value increase due to an explicit ID insertion?

A Step 2

B Step 3

C Step 4

D Step 5

💡 Hint

Look for the step where an explicit ID 5 is inserted and the AUTO_INCREMENT value jumps.

If you insert a new row without specifying ID after step 5, what ID will it get?

A 7

B 6

C 5

D 8

💡 Hint

Check the AUTO_INCREMENT value after step 5 in variable_tracker.

Concept Snapshot

AUTO_INCREMENT behavior:
- Define a column with AUTO_INCREMENT to auto-assign unique IDs.
- Insert rows without specifying the ID to get automatic numbering.
- Explicitly inserting a higher ID updates the AUTO_INCREMENT counter.
- AUTO_INCREMENT always increments by 1 after each insert.
- Useful for primary keys to ensure unique row IDs.

Full Transcript

This visual execution shows how the AUTO_INCREMENT feature works in SQL. When a table is created with an AUTO_INCREMENT column, the database starts counting from 1. Each time you insert a row without specifying the ID, the database assigns the current AUTO_INCREMENT value and then increases it by 1. If you insert a row with an explicit ID higher than the current counter, the AUTO_INCREMENT value jumps to one more than that ID. This ensures new inserts continue with unique IDs. The execution table tracks each step, showing the assigned IDs and how the counter changes. This helps beginners understand how automatic numbering works in databases.