Unique index behavior in SQL

Introduction

A unique index helps keep data clean by making sure no two rows have the same value in certain columns.

When you want to make sure no two users have the same email address.

When you want to prevent duplicate product codes in a store database.

When you want to ensure each employee has a unique ID number.

When you want to speed up searches on columns that must be unique.

When you want to avoid mistakes caused by duplicate entries.

Syntax

SQL

CREATE UNIQUE INDEX index_name ON table_name (column1, column2, ...);

A unique index can be created on one or more columns.

It prevents duplicate values in the indexed columns.

Examples

Creates a unique index on the email column in the users table to prevent duplicate emails.

SQL

CREATE UNIQUE INDEX idx_email ON users (email);

Ensures each product_code in the products table is unique.

SQL

CREATE UNIQUE INDEX idx_product_code ON products (product_code);

Prevents duplicate combinations of first and last names in the users table.

SQL

CREATE UNIQUE INDEX idx_user_fullname ON users (first_name, last_name);

Sample Program

This example creates a table employees with a unique index on the email column. The third insert fails because it tries to add a duplicate email.

SQL

CREATE TABLE employees (
  id INT PRIMARY KEY,
  email VARCHAR(100)
);

CREATE UNIQUE INDEX idx_email ON employees (email);

INSERT INTO employees (id, email) VALUES (1, '[e-mail]');
INSERT INTO employees (id, email) VALUES (2, '[e-mail]');
-- This next insert will fail because the email is already used
INSERT INTO employees (id, email) VALUES (3, '[e-mail]');

Important Notes

Unique indexes help keep your data accurate by stopping duplicates.

Trying to insert a duplicate value in a unique index column causes an error.

Unique indexes can also improve search speed on those columns.

Summary

Unique indexes prevent duplicate values in one or more columns.

They help keep data clean and consistent.

Inserting duplicates into unique indexed columns causes errors.

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