AUTO_INCREMENT behavior in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this AUTO_INCREMENT insert sequence?

Consider a table users with an id column set as AUTO_INCREMENT starting at 1. After inserting three rows, the id values are 1, 2, and 3. If we delete the row with id = 2 and then insert a new row, what will be the id of the new row?

SQL

CREATE TABLE users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50));
INSERT INTO users (name) VALUES ('Alice'), ('Bob'), ('Charlie');
DELETE FROM users WHERE id = 2;
INSERT INTO users (name) VALUES ('David');
SELECT id, name FROM users ORDER BY id;

A

1, Alice
2, Bob
3, Charlie
4, David

B

1, Alice
3, Charlie
4, David

C

1, Alice
2, David
3, Charlie

D

1, Alice
3, Charlie
2, David

💡 Hint

AUTO_INCREMENT does not reuse deleted IDs by default.

When a row with an AUTO_INCREMENT ID is deleted, the next inserted row gets a new ID incremented from the highest existing ID. It does not reuse the deleted ID.

🧠 Conceptual

intermediate

How does AUTO_INCREMENT behave after a server restart?

If a MySQL server is restarted, what happens to the AUTO_INCREMENT counter for a table that already has rows?

A The AUTO_INCREMENT counter continues from the highest existing ID plus one.

B The AUTO_INCREMENT counter starts from the last inserted ID, even if rows were deleted.

C The AUTO_INCREMENT counter is lost and must be manually reset.

D The AUTO_INCREMENT counter resets to 1 regardless of existing rows.

💡 Hint

Think about how the server remembers the last used ID.

On restart, MySQL reads the highest existing AUTO_INCREMENT value from the table and continues from there to avoid duplicate IDs.

📝 Syntax

advanced

Which statement correctly creates a table with AUTO_INCREMENT starting at 1000?

You want to create a table orders with an order_id column that auto-increments starting from 1000. Which SQL statement is correct?

A CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY START WITH 1000);

B CREATE TABLE orders (order_id INT PRIMARY KEY AUTO_INCREMENT=1000);

C CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT=1000;

D CREATE TABLE orders (order_id INT AUTO_INCREMENT PRIMARY KEY) START=1000;

💡 Hint

The AUTO_INCREMENT starting value is set outside the column definition.

In MySQL, the starting value for AUTO_INCREMENT is set as a table option after the column definitions using AUTO_INCREMENT=number.

🔧 Debug

advanced

Why does this insert fail with duplicate key error?

Given the table products with product_id as AUTO_INCREMENT primary key, the following insert causes a duplicate key error. Why? INSERT INTO products (product_id, name) VALUES (1, 'Pen');

SQL

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

A Because the name 'Pen' is a duplicate and violates a unique constraint.

B Because AUTO_INCREMENT columns cannot be explicitly assigned values.

C Because the table does not allow inserting any rows after the first.

D Because the value 1 for product_id already exists from the first insert.

💡 Hint

Check the existing IDs before the insert.

The first insert auto-assigned product_id = 1. Trying to insert another row with product_id = 1 causes a duplicate primary key error.

❓ optimization

expert

How to optimize bulk inserts with AUTO_INCREMENT to avoid gaps?

You need to insert 10,000 rows into a table with an AUTO_INCREMENT primary key. You want to minimize gaps in the IDs caused by transaction rollbacks or deletes. Which approach helps achieve this?

A Disable autocommit and insert all rows in a single transaction, then commit once.

B Insert rows one by one with autocommit enabled to ensure each ID is assigned sequentially.

C Manually assign IDs instead of using AUTO_INCREMENT to control gaps.

D Delete rows after insert to fill gaps in AUTO_INCREMENT IDs.

💡 Hint

Think about how transactions affect AUTO_INCREMENT values.

Using a single transaction for bulk inserts avoids gaps caused by rollbacks because IDs are only assigned once the transaction commits.