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.