Unique index behavior in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Effect of Unique Index on Duplicate Inserts

Given a table users with a unique index on the email column, what happens when you try to insert two rows with the same email?

SQL

CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(255));
CREATE UNIQUE INDEX idx_email ON users(email);

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

A The second insert is ignored silently without error.

B Both inserts succeed and the table contains two rows with the same email.

C The second insert overwrites the first row with the same email.

D The second insert fails with a unique constraint violation error.

💡 Hint

Think about what a unique index enforces on the column values.

A unique index prevents duplicate values in the indexed column. Trying to insert a duplicate value causes an error.

🧠 Conceptual

intermediate

Unique Index vs Primary Key

Which statement correctly describes the difference between a unique index and a primary key in SQL?

A A primary key uniquely identifies each row and cannot be NULL; a unique index allows NULLs and enforces uniqueness.

B A unique index can only be created on numeric columns; a primary key can be any data type.

C A primary key allows duplicate values; a unique index does not.

D A unique index automatically creates a primary key.

💡 Hint

Consider NULL values and uniqueness rules.

Primary keys enforce uniqueness and disallow NULLs. Unique indexes enforce uniqueness but allow multiple NULLs depending on the database.

📝 Syntax

advanced

Creating a Unique Index on Multiple Columns

Which SQL statement correctly creates a unique index on the combination of first_name and last_name columns in the employees table?

A CREATE UNIQUE INDEX idx_name ON employees(first_name, last_name);

B CREATE UNIQUE KEY idx_name ON employees(first_name, last_name);

C CREATE INDEX UNIQUE idx_name ON employees(first_name, last_name);

D CREATE UNIQUE CONSTRAINT idx_name ON employees(first_name, last_name);

💡 Hint

Focus on the correct syntax order for creating unique indexes.

The correct syntax is CREATE UNIQUE INDEX index_name ON table(columns). Other options have incorrect keyword order or invalid syntax.

❓ optimization

advanced

Performance Impact of Unique Indexes

How does adding a unique index on a frequently updated column affect database performance?

A It has no impact on performance since indexes are only used during SELECT queries.

B It speeds up insert and update operations by automatically sorting the data.

C It can slow down insert and update operations because the database must check uniqueness on each change.

D It causes the database to lock the entire table on every update.

💡 Hint

Think about what the database must do to maintain uniqueness.

Unique indexes require the database to verify no duplicates exist on each insert or update, which adds overhead.

🔧 Debug

expert

Diagnosing Unique Constraint Violation

You have a unique index on the username column. After deleting a row with username 'john', you try to insert a new row with username 'john' but get a unique constraint violation error. What is the most likely cause?

A The insert statement has a syntax error causing the violation error.

B The deleted row is still locked or not fully committed, so the unique index still sees the old value.

C Unique indexes do not allow reusing deleted values under any circumstances.

D The username column is case-sensitive and 'john' differs from the deleted row's username.

💡 Hint

Consider transaction states and locking behavior.

If the delete is not committed or the row is locked, the unique index still considers the old value present, causing the violation.