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.

Practice

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

easy

A. Encrypts the data in the column

B. Deletes duplicate rows automatically

C. Automatically assigns a unique number to new rows

D. Prevents any new rows from being added

Solution

1. Step 1: Understand the purpose of AUTO_INCREMENT

   AUTO_INCREMENT is used to generate a unique number for each new row automatically, usually for primary keys.

2. Step 2: Compare options with this behavior

   Only Automatically assigns a unique number to new rows describes this behavior correctly; others describe unrelated actions.

3. Final Answer:

   Automatically assigns a unique number to new rows -> Option C

4. Quick Check:

   AUTO_INCREMENT = unique number assignment [OK]

Hint: AUTO_INCREMENT means unique numbers auto-assigned [OK]

Common Mistakes:

• Thinking AUTO_INCREMENT deletes duplicates
• Confusing AUTO_INCREMENT with encryption
• Assuming AUTO_INCREMENT blocks inserts

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

easy

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

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

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

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

Solution

1. Step 1: Review correct AUTO_INCREMENT syntax

   The correct syntax is to declare the column as INT AUTO_INCREMENT and set it as PRIMARY KEY.

2. Step 2: Check each option for syntax errors

   CREATE TABLE users (id INT AUTO_INCREMENT, name VARCHAR(50), PRIMARY KEY(id)); matches the correct syntax. Options B, C, and D use invalid keywords or misplaced PRIMARY KEY.

3. Final Answer:

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

4. Quick Check:

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

Hint: AUTO_INCREMENT goes with INT and PRIMARY KEY [OK]

Common Mistakes:

• Using wrong keywords like INCREMENT_AUTO
• Placing PRIMARY KEY inside column definition incorrectly
• Adding suffixes like AUTO_INCREMENTED

3. Given the table products with id INT AUTO_INCREMENT PRIMARY KEY and name VARCHAR(50), what will be the id values after these inserts?

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

medium

A. [1, 2, 3]

B. [0, 1, 2]

C. [NULL, NULL, NULL]

D. [1, 1, 1]

Solution

1. Step 1: Understand AUTO_INCREMENT numbering

   Each insert without specifying id gets the next unique number starting from 1 by default.

2. Step 2: Apply to the three inserts

   First insert gets 1, second gets 2, third gets 3 as id values.

3. Final Answer:

   [1, 2, 3] -> Option A

4. Quick Check:

   Sequential AUTO_INCREMENT IDs = [1, 2, 3] [OK]

Hint: AUTO_INCREMENT starts at 1 and increments by 1 [OK]

Common Mistakes:

• Assuming IDs start at 0
• Expecting NULL values for AUTO_INCREMENT
• Thinking IDs repeat for each insert

4. You have a table orders with order_id INT AUTO_INCREMENT PRIMARY KEY. You try to insert a row with order_id = 0 manually, but get an error. What is the likely cause?

medium

A. You cannot insert a value manually into an AUTO_INCREMENT column without special syntax

B. The table does not allow any inserts

C. The column is not set as PRIMARY KEY

D. The AUTO_INCREMENT value must be a string

Solution

1. Step 1: Understand manual inserts on AUTO_INCREMENT columns

   By default, inserting a value manually into an AUTO_INCREMENT column can cause errors unless allowed explicitly.

2. Step 2: Analyze the error cause

   The error likely occurs because the database expects to manage the AUTO_INCREMENT values automatically, and manual insertion conflicts with this.

3. Final Answer:

   You cannot insert a value manually into an AUTO_INCREMENT column without special syntax -> Option A

4. Quick Check:

   Manual insert on AUTO_INCREMENT needs care = You cannot insert a value manually into an AUTO_INCREMENT column without special syntax [OK]

Hint: AUTO_INCREMENT columns usually reject manual values [OK]

Common Mistakes:

• Assuming inserts are blocked entirely
• Confusing PRIMARY KEY with AUTO_INCREMENT behavior
• Thinking AUTO_INCREMENT accepts strings

5. A table employees has emp_id INT AUTO_INCREMENT PRIMARY KEY. After deleting the row with emp_id = 3, you insert a new row without specifying emp_id. What will be the emp_id of the new row?

hard

A. 3

B. NULL

C. 1

D. The next highest number after the last inserted ID

Solution

1. Step 1: Understand AUTO_INCREMENT behavior after deletions

   Deleting a row does not reset or reuse the deleted AUTO_INCREMENT value by default.

2. Step 2: Determine the new ID assigned

   The new row gets the next number after the highest previously assigned ID, not the deleted one.

3. Final Answer:

   The next highest number after the last inserted ID -> Option D

4. Quick Check:

   Deleted IDs are not reused automatically = The next highest number after the last inserted ID [OK]

Hint: AUTO_INCREMENT never reuses deleted IDs by default [OK]

Common Mistakes:

• Assuming deleted IDs get reused automatically
• Thinking new inserts reset to 1
• Expecting NULL for AUTO_INCREMENT columns