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.

Practice

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

easy

A. To store data in alphabetical order

B. To speed up data insertion

C. To allow duplicate values for faster queries

D. To prevent duplicate values in one or more columns

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 describe unrelated behaviors.

3. Final Answer:

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

4. Quick Check:

   UNIQUE index = prevent duplicates [OK]

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

Common Mistakes:

• Thinking UNIQUE speeds up insertion
• Confusing UNIQUE with sorting
• Believing UNIQUE allows duplicates

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

easy

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

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

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

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

Solution

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

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

2. Step 2: Match options to syntax

   CREATE UNIQUE INDEX idx_email ON users(email); matches exactly. CREATE INDEX UNIQUE idx_email ON users(email); has wrong keyword order. CREATE UNIQUE KEY idx_email ON users(email); uses UNIQUE KEY which is for constraints, not index creation. CREATE UNIQUE TABLE idx_email ON users(email); is invalid syntax.

3. Final Answer:

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

4. Quick Check:

   CREATE UNIQUE INDEX = CREATE UNIQUE INDEX idx_email ON users(email); [OK]

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

Common Mistakes:

• Swapping UNIQUE and INDEX keywords
• Using UNIQUE KEY instead of UNIQUE INDEX
• Trying to create UNIQUE TABLE (invalid)

3. Given the table products with a unique index on sku, what happens when you run this SQL?

INSERT INTO products (sku, name) VALUES ('A123', 'Item1');
INSERT INTO products (sku, name) VALUES ('A123', 'Item2');

medium

A. Second insert fails with a duplicate key error

B. Both inserts fail due to unique index

C. First insert fails, second succeeds

D. Both rows insert successfully

Solution

1. Step 1: Understand unique index effect on inserts

   The unique index on sku means no two rows can have the same sku value.

2. Step 2: Analyze the insert statements

   The first insert adds 'A123' successfully. The second tries to insert 'A123' again, violating uniqueness, causing an error.

3. Final Answer:

   Second insert fails with a duplicate key error -> Option A

4. Quick Check:

   Duplicate insert = error [OK]

Hint: Duplicate value in UNIQUE column causes insert error [OK]

Common Mistakes:

• Assuming duplicates are allowed
• Thinking first insert fails
• Believing both inserts fail

4. You have a unique index on the username column. The following query causes an error:

INSERT INTO users (username) VALUES ('john');
INSERT INTO users (username) VALUES ('john');

What is the best way to fix this error?

medium

A. Change the second insert to a different username

B. Remove the unique index on username

C. Insert NULL instead of 'john' in the second insert

D. Use a SELECT query instead of INSERT

Solution

1. Step 1: Identify cause of error

   The unique index prevents duplicate usernames. Inserting 'john' twice violates this.

2. Step 2: Choose a fix that respects uniqueness

   Changing the second insert to a different username avoids duplicates and keeps data integrity.

3. Final Answer:

   Change the second insert to a different username -> Option A

4. Quick Check:

   Fix duplicates by changing values [OK]

Hint: Change duplicate value to unique to fix insert error [OK]

Common Mistakes:

• Removing unique index (loses data integrity)
• Inserting NULL (may not be allowed or unique)
• Using SELECT instead of INSERT (doesn't fix duplicates)

5. Consider a table employees with a unique index on (email, department_id). Which of these inserts will cause a unique index violation if the table already has a row with email='[e-mail]' and department_id=10?

hard

A. INSERT INTO employees (email, department_id) VALUES ('[e-mail]', 10);

B. INSERT INTO employees (email, department_id) VALUES ('[e-mail]', 11);

C. INSERT INTO employees (email, department_id) VALUES ('[e-mail]', 10);

D. INSERT INTO employees (email, department_id) VALUES ('[e-mail]', 11);

Solution

1. Step 1: Understand composite unique index

   The unique index on (email, department_id) means the combination of both values must be unique.

2. Step 2: Check each insert against existing row

   Only INSERT INTO employees (email, department_id) VALUES ('[e-mail]', 10); duplicates both email and department_id, causing violation. Others differ in at least one value, so allowed.

3. Final Answer:

   INSERT with email='[e-mail]' and department_id=10 causes violation -> Option C

4. Quick Check:

   Composite UNIQUE = unique pairs [OK]

Hint: Composite unique index blocks duplicate value pairs [OK]

Common Mistakes:

• Thinking unique applies to each column separately
• Assuming different department_id allows duplicate email
• Ignoring composite key uniqueness