AUTO_INCREMENT behavior in SQL - Mini Project: Build & Apply

Understanding AUTO_INCREMENT Behavior in SQL

📖 Scenario: You are managing a small online store database. You want to create a table to store product information. Each product should have a unique ID that automatically increases whenever a new product is added, so you don't have to manually assign IDs.

🎯 Goal: Build a SQL table with an AUTO_INCREMENT column for product IDs, insert some products, and observe how the IDs are assigned automatically.

📋 What You'll Learn

Create a table named products with columns id, name, and price

Set the id column as an AUTO_INCREMENT primary key

Insert three products with names and prices but without specifying id

Query the table to see the automatically assigned id values

💡 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 handle unique identifiers efficiently without manual input, a key 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 these columns: id as an integer primary key with AUTO_INCREMENT, name as a VARCHAR(50), and price as a DECIMAL(5,2).

SQL

-- Write the CREATE TABLE statement for products with id as AUTO_INCREMENT primary key
-- Your code here

Hint

Use CREATE TABLE with id INT AUTO_INCREMENT PRIMARY KEY to make the ID auto-increment.

2

Insert products without specifying IDs

Write three SQL INSERT INTO products (name, price) statements to add these products: 'Pen' priced 1.20, 'Notebook' priced 2.50, and 'Eraser' priced 0.80. Do not include the id column in the inserts.

SQL

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(5,2)
);
-- Insert three products without id
-- Your code here

Hint

Use INSERT INTO products (name, price) VALUES (...) without the id column.

3

Query the products table to see AUTO_INCREMENT IDs

Write a SQL SELECT statement to get all columns from the products table to see the automatically assigned id values.

SQL

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO products (name, price) VALUES ('Pen', 1.20);
INSERT INTO products (name, price) VALUES ('Notebook', 2.50);
INSERT INTO products (name, price) VALUES ('Eraser', 0.80);

-- Select all columns from products
-- Your code here

Hint

Use SELECT * FROM products; to see all rows and columns.

4

Add a new product and observe AUTO_INCREMENT continues

Write a SQL INSERT INTO products (name, price) statement to add a new product 'Marker' priced 1.50. Then write a SELECT * FROM products statement to see the new product with the next id.

SQL

CREATE TABLE products (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50),
  price DECIMAL(5,2)
);

INSERT INTO products (name, price) VALUES ('Pen', 1.20);
INSERT INTO products (name, price) VALUES ('Notebook', 2.50);
INSERT INTO products (name, price) VALUES ('Eraser', 0.80);

SELECT * FROM products;

-- Insert 'Marker' priced 1.50
-- Select all products again
-- Your code here

Hint

Insert the new product without id and then select all rows again.

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