Unique index behavior in SQL - Mini Project: Build & Apply

Understanding Unique Index Behavior in SQL

📖 Scenario: You are managing a small online store database. You want to ensure that each product has a unique product code so that no two products can share the same code.

🎯 Goal: Build a simple SQL table with a unique index on the product code column to prevent duplicate product codes.

📋 What You'll Learn

Create a table named products with columns id (integer primary key), product_code (text), and name (text).

Add a unique index on the product_code column.

Insert sample data with unique product codes.

Attempt to insert a duplicate product code to understand unique index behavior.

💡 Why This Matters

🌍 Real World

Unique indexes are used in databases to ensure data integrity by preventing duplicate entries in important columns like product codes, user emails, or IDs.

💼 Career

Database administrators and developers use unique indexes to enforce business rules and maintain clean, reliable data.

1

Create the products table

Write a SQL statement to create a table called products with three columns: id as an integer primary key, product_code as text, and name as text.

SQL

-- Your code here to create the products table

Hint

Use CREATE TABLE with the specified columns and types. Remember to set id as the primary key.

2

Add a unique index on product_code

Write a SQL statement to create a unique index named idx_unique_product_code on the product_code column of the products table.

SQL

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    product_code TEXT,
    name TEXT
);
-- Your code here to create a unique index on product_code

Hint

Use CREATE UNIQUE INDEX with the index name and specify the column product_code.

3

Insert sample unique data

Write SQL INSERT statements to add these two products into the products table: (1, 'P1001', 'Red Shirt') and (2, 'P1002', 'Blue Jeans').

SQL

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    product_code TEXT,
    name TEXT
);

CREATE UNIQUE INDEX idx_unique_product_code ON products(product_code);
-- Your code here to insert two unique products

Hint

Use two separate INSERT INTO statements with the exact values given.

4

Try inserting a duplicate product_code

Write a SQL INSERT statement to add a product with id 3, product_code 'P1001', and name 'Green Hat'. This will test the unique index behavior.

SQL

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    product_code TEXT,
    name TEXT
);

CREATE UNIQUE INDEX idx_unique_product_code ON products(product_code);

INSERT INTO products (id, product_code, name) VALUES (1, 'P1001', 'Red Shirt');
INSERT INTO products (id, product_code, name) VALUES (2, 'P1002', 'Blue Jeans');
-- Your code here to insert a product with duplicate product_code 'P1001'

Hint

Try inserting a product with the same product_code 'P1001' to see how the unique index prevents duplicates.

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