Unique index behavior in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a unique index in a database?

A unique index ensures that all values in the indexed column(s) are different. It prevents duplicate values in those columns.

intermediate

How does a unique index differ from a primary key?

A primary key is a unique index that also uniquely identifies each row and cannot contain NULL values. A unique index allows NULLs unless specified otherwise.

beginner

What happens if you try to insert a duplicate value into a column with a unique index?

The database will reject the insert and return an error because the unique index prevents duplicate values.

intermediate

Can a unique index be created on multiple columns? What does it enforce?

Yes, a unique index can cover multiple columns. It enforces that the combination of values across those columns is unique for each row.

beginner

Does a unique index improve query performance? How?

Yes, a unique index helps the database find rows faster when searching by the indexed column(s), because it organizes data for quick lookups.

What does a unique index prevent in a database table?

A Duplicate values in the indexed column(s)

B NULL values in any column

C All rows from being deleted

D Multiple tables from being joined

A unique index prevents duplicate values in the indexed column(s), ensuring each value is unique.

Which of the following is true about a unique index?

A It can be created on multiple columns

B It allows duplicate values

C It automatically creates a primary key

D It disables data insertion

A unique index can be created on multiple columns to enforce uniqueness on the combination of those columns.

What error occurs if you insert a duplicate value into a unique indexed column?

A Syntax error

B Duplicate key error

C Connection error

D Timeout error

The database returns a duplicate key error because the unique index forbids duplicate values.

Does a unique index allow NULL values by default?

A Only if the column is a primary key

B No, it never allows NULLs

C Only in MySQL

D Yes, usually it allows NULLs

Unique indexes usually allow NULL values unless the column is defined as NOT NULL.

How does a unique index affect query speed?

A Slows down queries

B Has no effect

C Speeds up searches on indexed columns

D Prevents queries from running

A unique index speeds up searches by organizing data for quick lookups on the indexed columns.

Explain what a unique index is and how it helps maintain data integrity.

Think about how a unique index stops repeated values in a column.

Describe the difference between a unique index and a primary key.

Consider NULL values and row identification.

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