Unique indexes in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this query with a unique index?

Consider a table users with columns id (primary key) and email. A unique index is created on email. What happens when you run this insert query?

MySQL

INSERT INTO users (id, email) VALUES (1, '[e-mail]'), (2, '[e-mail]'), (3, '[e-mail]');

A The query inserts all three rows successfully.

B The query fails immediately without inserting any rows because of duplicate email.

C The query inserts the first two rows and fails on the third due to duplicate email.

D The query inserts only the first row and skips the rest silently.

💡 Hint

Think about how unique indexes enforce uniqueness during batch inserts.

A unique index prevents duplicate values in the indexed column. When inserting multiple rows in one query, if any row violates the unique constraint, the entire query fails and no rows are inserted.

🧠 Conceptual

intermediate

Why use a unique index instead of a primary key?

Which of the following is a valid reason to create a unique index on a column instead of making it a primary key?

A Because a table can have multiple unique indexes but only one primary key.

B Because unique indexes allow NULL values but primary keys do not.

C Because unique indexes automatically create foreign key relationships.

D Because primary keys do not enforce uniqueness.

💡 Hint

Think about the number of primary keys allowed per table.

A table can have only one primary key but can have multiple unique indexes on different columns. Unique indexes enforce uniqueness but allow more flexibility than primary keys.

📝 Syntax

advanced

Which statement correctly creates a unique index?

Choose the correct SQL statement to create a unique index named idx_unique_username on the username column of the accounts table.

A CREATE INDEX UNIQUE idx_unique_username ON accounts (username);

B CREATE UNIQUE KEY idx_unique_username ON accounts (username);

C CREATE UNIQUE INDEX idx_unique_username ON accounts (username);

D CREATE UNIQUE CONSTRAINT idx_unique_username ON accounts (username);

💡 Hint

Check the correct order of keywords in MySQL syntax for unique indexes.

The correct syntax to create a unique index in MySQL is CREATE UNIQUE INDEX index_name ON table_name (column);. Option C has wrong keyword order, C and D are invalid syntax for index creation.

❓ optimization

advanced

How does a unique index improve query performance?

Which of the following best explains how a unique index can improve query speed?

A It automatically caches all rows with unique values in memory for faster access.

B It compresses the data in the column to reduce disk space and speed up queries.

C It disables locking on the table, allowing faster concurrent reads.

D It allows the database to quickly locate rows because it knows values are unique and can stop searching early.

💡 Hint

Think about how uniqueness helps the database find data faster.

A unique index lets the database engine stop searching once it finds a matching value because duplicates cannot exist, speeding up lookups.

🔧 Debug

expert

Why does this unique index creation fail?

Given the table employees with existing data, the following command fails. Why? CREATE UNIQUE INDEX idx_unique_email ON employees (email);

MySQL

Table employees has duplicate emails in the email column.

A Because the email column contains duplicate values violating the unique index constraint.

B Because the syntax for creating a unique index is incorrect.

C Because the employees table already has a primary key.

D Because unique indexes cannot be created on columns with NULL values.

💡 Hint

Think about what data must look like before creating a unique index.

A unique index requires all values in the indexed column to be unique. If duplicates exist, the index creation fails.

Practice

1. What is the main purpose of a UNIQUE index in MySQL?

easy

A. To prevent duplicate values in one or more columns

B. To speed up data insertion

C. To allow duplicate values in a column

D. To delete duplicate rows automatically

Solution

1. Step 1: Understand the role of UNIQUE index

   A UNIQUE index ensures that no two rows have the same value in the indexed column(s).

2. Step 2: Compare options with this definition

   Only To prevent duplicate values in one or more columns correctly states that UNIQUE prevents duplicates. Others are incorrect or unrelated.

3. Final Answer:

   To prevent duplicate values in one or more columns -> Option A

4. Quick Check:

   UNIQUE index = prevent duplicates [OK]

Hint: UNIQUE means no duplicates allowed in indexed columns [OK]

Common Mistakes:

• Thinking UNIQUE speeds up inserts
• Believing UNIQUE allows duplicates
• Confusing UNIQUE with automatic deletion

2. Which of the following is the correct syntax to create a UNIQUE index on the column email in a table named users?

easy

A. CREATE UNIQUE KEY idx_email FROM users(email);

B. CREATE INDEX UNIQUE idx_email ON users(email);

C. CREATE UNIQUE INDEX idx_email ON users(email);

D. CREATE UNIQUE ON users INDEX idx_email(email);

Solution

1. Step 1: Recall correct syntax for UNIQUE index creation

   The correct syntax is: CREATE UNIQUE INDEX index_name ON table_name(column_name);

2. Step 2: Match options with correct syntax

   CREATE UNIQUE INDEX idx_email ON users(email); matches exactly. Others have wrong keyword order or syntax errors.

3. Final Answer:

   CREATE UNIQUE INDEX idx_email ON users(email); -> Option C

4. Quick Check:

   CREATE UNIQUE INDEX ... ON ... (column) [OK]

Hint: Remember: CREATE UNIQUE INDEX name ON table(column) [OK]

Common Mistakes:

• Swapping UNIQUE and INDEX keywords
• Using FROM instead of ON
• Incorrect keyword order

3. Given the table employees with a UNIQUE index on employee_id, what happens if you run this query?

INSERT INTO employees (employee_id, name) VALUES (101, 'Alice');
INSERT INTO employees (employee_id, name) VALUES (101, 'Bob');

medium

A. The second insert fails with a duplicate key error

B. Both rows are inserted successfully

C. The second insert overwrites the first row

D. The second insert is ignored silently

Solution

1. Step 1: Understand UNIQUE index effect on inserts

   A UNIQUE index on employee_id prevents duplicate values in that column.

2. Step 2: Analyze the inserts

   The first insert adds employee_id 101. The second insert tries to add the same employee_id 101, violating the UNIQUE constraint, causing an error.

3. Final Answer:

   The second insert fails with a duplicate key error -> Option A

4. Quick Check:

   UNIQUE index blocks duplicate inserts [OK]

Hint: Duplicate insert on UNIQUE column causes error [OK]

Common Mistakes:

• Assuming duplicates are allowed
• Thinking second insert overwrites first
• Believing second insert is ignored silently

4. You tried to create a UNIQUE index on the column username but got an error. Which of these is the most likely cause?

medium

A. The table does not exist

B. The username column contains duplicate values

C. You forgot to specify the index name

D. UNIQUE indexes are not supported on text columns

Solution

1. Step 1: Understand UNIQUE index creation requirements

   Creating a UNIQUE index requires all existing values in the column to be unique.

2. Step 2: Identify common error causes

   If duplicates exist, MySQL throws an error when creating the UNIQUE index.

3. Final Answer:

   The username column contains duplicate values -> Option B

4. Quick Check:

   Duplicates block UNIQUE index creation [OK]

Hint: Check for duplicates before creating UNIQUE index [OK]

Common Mistakes:

• Assuming index name is mandatory
• Believing UNIQUE not allowed on text columns
• Ignoring existing duplicates

5. You want to ensure that the combination of first_name and last_name in the contacts table is unique, but duplicates are still appearing. Which is the best way to fix this?

hard

A. Use a CHECK constraint to prevent duplicates

B. Create separate UNIQUE indexes on first_name and last_name individually

C. Add a PRIMARY KEY on first_name

D. Create a UNIQUE index on both first_name and last_name columns together

Solution

1. Step 1: Understand uniqueness on multiple columns

   To ensure uniqueness on a combination, a composite UNIQUE index on both columns is needed.

2. Step 2: Analyze other options

   Separate UNIQUE indexes allow duplicates across combinations. PRIMARY KEY on one column doesn't cover both. CHECK constraints can't enforce uniqueness.

3. Final Answer:

   Create a UNIQUE index on both first_name and last_name columns together -> Option D

4. Quick Check:

   Composite UNIQUE index = unique pairs [OK]

Hint: Use composite UNIQUE index for multi-column uniqueness [OK]

Common Mistakes:

• Creating separate UNIQUE indexes instead of composite
• Using PRIMARY KEY on one column only
• Relying on CHECK constraints for uniqueness