AUTO_INCREMENT behavior in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the next AUTO_INCREMENT value after insert?

Consider this MySQL table and inserts:

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20));
INSERT INTO users (name) VALUES ('Alice'), ('Bob');
INSERT INTO users (id, name) VALUES (5, 'Charlie');
INSERT INTO users (name) VALUES ('David');

What will be the id of 'David'?

MySQL

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20));
INSERT INTO users (name) VALUES ('Alice'), ('Bob');
INSERT INTO users (id, name) VALUES (5, 'Charlie');
INSERT INTO users (name) VALUES ('David');
SELECT * FROM users ORDER BY id;

A 6

B 3

C 7

D 5

💡 Hint

Remember that AUTO_INCREMENT will use the highest existing value plus one.

After inserting 'Charlie' with id 5, the AUTO_INCREMENT counter moves to 6. So 'David' gets id 6.

🧠 Conceptual

intermediate

What happens when you delete the highest AUTO_INCREMENT row?

Given a table with AUTO_INCREMENT primary key, if you delete the row with the highest id, what will be the next inserted row's id?

A It throws an error on insert

B It continues with the next number after the deleted id

C It resets to 1

D It reuses the deleted highest id

💡 Hint

Think about how AUTO_INCREMENT keeps track internally.

Deleting rows does not reset AUTO_INCREMENT. It continues from the highest value it has seen.

📝 Syntax

advanced

Which statement correctly resets AUTO_INCREMENT to 1?

You want to reset the AUTO_INCREMENT counter of a table named orders to 1. Which SQL statement is correct?

A ALTER TABLE orders AUTO_INCREMENT = 1;

B UPDATE orders SET AUTO_INCREMENT = 1;

C RESET AUTO_INCREMENT orders TO 1;

D SET orders.AUTO_INCREMENT = 1;

💡 Hint

Look for the correct syntax to alter table properties.

Only ALTER TABLE with AUTO_INCREMENT = value is valid syntax to reset the counter.

❓ optimization

advanced

How to avoid gaps in AUTO_INCREMENT after deletes?

You want to keep AUTO_INCREMENT values continuous without gaps after deleting rows. Which approach is best?

A Use RESTART IDENTITY in DELETE statements

B Use transactions to rollback deletes

C Manually update AUTO_INCREMENT value after deletes

D Do not delete rows; mark them as inactive instead

💡 Hint

Think about how AUTO_INCREMENT works and what causes gaps.

Deleting rows creates gaps. Marking rows inactive avoids gaps without resetting AUTO_INCREMENT.

🔧 Debug

expert

Why does this insert fail with duplicate key error?

Given this table and inserts:

CREATE TABLE products (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20));
INSERT INTO products (id, name) VALUES (1, 'Pen');
INSERT INTO products (name) VALUES ('Pencil');
INSERT INTO products (id, name) VALUES (1, 'Eraser');

Why does the last insert fail?

MySQL

CREATE TABLE products (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(20));
INSERT INTO products (id, name) VALUES (1, 'Pen');
INSERT INTO products (name) VALUES ('Pencil');
INSERT INTO products (id, name) VALUES (1, 'Eraser');

A Because AUTO_INCREMENT cannot be used with explicit id values

B Because 'Eraser' is a duplicate name

C Because id 1 already exists, causing duplicate primary key error

D Because the table does not allow inserts without id

💡 Hint

Check the primary key constraints and inserted values.

Inserting id 1 again causes a duplicate key error because id 1 already exists.