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.

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