AUTO_INCREMENT behavior in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a table with an auto-incrementing primary key.

MySQL

CREATE TABLE users (id INT [1] PRIMARY KEY, name VARCHAR(100));

Drag options to blanks, or click blank then click option'

AAUTO_INCREMENT

BNOT NULL

CUNIQUE

DDEFAULT 0

Attempts:

3 left

💡 Hint

Common Mistakes

Using NOT NULL instead of AUTO_INCREMENT will not auto-generate values.

Forgetting to set the column as PRIMARY KEY with AUTO_INCREMENT.

The AUTO_INCREMENT keyword makes the id column automatically increase by 1 for each new row.

2fill in blank

medium

Complete the code to insert a new user without specifying the auto-increment column.

MySQL

INSERT INTO users (name) VALUES ([1]);

Drag options to blanks, or click blank then click option'

ANULL

B1

C'id'

D'Alice'

Attempts:

3 left

💡 Hint

Common Mistakes

Trying to insert a value into the auto-increment column manually.

Using NULL as a value for the name column.

When inserting, you do not specify the id column; the database assigns it automatically. Here, we insert the name 'Alice'.

3fill in blank

hard

Fix the error in the insert statement to let auto-increment work correctly.

MySQL

INSERT INTO users (id, name) VALUES ([1], 'Bob');

Drag options to blanks, or click blank then click option'

A0

BNULL

CDEFAULT

D1

Attempts:

3 left

💡 Hint

Common Mistakes

Inserting a fixed number instead of NULL for the auto-increment column.

Using DEFAULT keyword which is not valid in VALUES clause.

To let MySQL auto-generate the id, insert NULL or omit the column. Using NULL tells MySQL to assign the next auto-increment value.

4fill in blank

hard

Fill both blanks to reset the auto-increment counter to 1 for the table.

MySQL

ALTER TABLE users [1] = [2];

Drag options to blanks, or click blank then click option'

AAUTO_INCREMENT

B1

CRESET

DSTART

Attempts:

3 left

💡 Hint

Common Mistakes

Using RESET or START keywords which are not valid in this context.

Trying to set AUTO_INCREMENT without a value.

To reset the auto-increment counter, use ALTER TABLE users AUTO_INCREMENT = 1;. This sets the next auto-increment value to 1.

5fill in blank

hard

Fill all three blanks to create a table with an auto-increment column, insert a row, and select the auto-generated id.

MySQL

CREATE TABLE products (product_id INT [1] PRIMARY KEY, name VARCHAR(50));
INSERT INTO products (name) VALUES ([2]);
SELECT [3] FROM products WHERE name = 'Table';

Drag options to blanks, or click blank then click option'

AAUTO_INCREMENT

B'Table'

Cproduct_id

DNOT NULL

Attempts:

3 left

💡 Hint

Common Mistakes

Omitting AUTO_INCREMENT in the table definition.

Inserting the id manually instead of letting it auto-increment.

Selecting the wrong column in the SELECT statement.

First, create the table with AUTO_INCREMENT on product_id. Then insert a product named 'Table'. Finally, select the product_id for that product.

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