AUTO_INCREMENT behavior in MySQL - Mini Project: Build & Apply

Understanding AUTO_INCREMENT Behavior in MySQL

📖 Scenario: You are managing a small online store database. You want to create a table to store product information where each product has a unique ID that automatically increases whenever a new product is added.

🎯 Goal: Build a MySQL table called products with an id column that uses AUTO_INCREMENT to assign unique IDs automatically. Insert some products and observe how the IDs increment.

📋 What You'll Learn

Create a table named products with columns id (integer, primary key, auto-increment) and name (varchar).

Set the id column to use AUTO_INCREMENT.

Insert three products with names exactly: 'Pen', 'Notebook', and 'Eraser'.

Query the table to see the id and name of all products.

💡 Why This Matters

🌍 Real World

AUTO_INCREMENT is commonly used in databases to assign unique IDs to records automatically, such as user IDs, order numbers, or product IDs.

💼 Career

Understanding AUTO_INCREMENT helps in designing databases that require unique identifiers without manual input, a fundamental skill for database administrators and backend developers.

1

Create the products table with AUTO_INCREMENT

Write a SQL statement to create a table called products with two columns: id as an integer primary key with AUTO_INCREMENT, and name as a varchar(50).

MySQL

-- Your SQL code here to create the products table with id as AUTO_INCREMENT primary key and name as varchar(50)

Hint

Use CREATE TABLE with id INT AUTO_INCREMENT PRIMARY KEY and name VARCHAR(50).

2

Insert products into the table

Write three SQL INSERT statements to add products with names 'Pen', 'Notebook', and 'Eraser' into the products table. Do not specify the id values.

MySQL

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);
-- Insert 'Pen', 'Notebook', and 'Eraser' into products
-- Your SQL insert statements here

Hint

Use INSERT INTO products (name) VALUES ('ProductName'); for each product.

3

Query the products table to see AUTO_INCREMENT IDs

Write a SQL SELECT statement to retrieve the id and name columns from the products table to see the automatically assigned IDs.

MySQL

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

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

-- Select id and name from products
-- Your SQL select statement here

Hint

Use SELECT id, name FROM products; to see the table contents.

4

Reset AUTO_INCREMENT value

Write a SQL statement to reset the AUTO_INCREMENT counter of the products table to start from 1 again.

MySQL

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50)
);

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

SELECT id, name FROM products;

-- Reset AUTO_INCREMENT to 1
-- Your SQL statement here

Hint

Use ALTER TABLE products AUTO_INCREMENT = 1; to reset the counter.

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