Unique index behavior in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a unique index on the column 'email' in the 'users' table.

SQL

CREATE UNIQUE INDEX [1] ON users(email);

Drag options to blanks, or click blank then click option'

Aunique_email_index

Busers_email_idx

Cidx_email_unique

Demail_idx

Attempts:

3 left

💡 Hint

Common Mistakes

Using a non-descriptive index name.

Omitting the UNIQUE keyword (not applicable here but common).

The index name idx_email_unique clearly indicates it is a unique index on the email column.

2fill in blank

medium

Complete the code to insert a new user with email '[e-mail]' into the 'users' table.

SQL

INSERT INTO users (name, email) VALUES ('Alice', [1]);

Drag options to blanks, or click blank then click option'

A'[e-mail]'

B'[e-mail]'

C'[e-mail]'

D'[e-mail]'

Attempts:

3 left

💡 Hint

Common Mistakes

Using a different email than specified.

Forgetting to put quotes around the email.

The email '[e-mail]' matches the requirement for this insertion.

3fill in blank

hard

Fix the error in the query to select all users with unique emails.

SQL

SELECT * FROM users WHERE email [1] (SELECT email FROM users GROUP BY email HAVING COUNT(*) = 1);

Drag options to blanks, or click blank then click option'

AIN

B=

CLIKE

DNOT IN

Attempts:

3 left

💡 Hint

Common Mistakes

Using '=' which expects a single value.

Using 'LIKE' which is for pattern matching.

The IN operator is used to check if the email is in the list of emails that appear only once.

4fill in blank

hard

Fill both blanks to create a unique index on 'username' and 'email' columns in the 'accounts' table.

SQL

CREATE UNIQUE INDEX [1] ON accounts([2]);

Drag options to blanks, or click blank then click option'

Aidx_username_email_unique

Busername

Cusername, email

Demail

Attempts:

3 left

💡 Hint

Common Mistakes

Using only one column instead of both.

Using a non-descriptive index name.

The index name idx_username_email_unique is descriptive. The columns username, email are both included for uniqueness.

5fill in blank

hard

Fill all three blanks to select emails that appear more than once in the 'subscribers' table.

SQL

SELECT [1], COUNT([2]) FROM subscribers GROUP BY [3] HAVING COUNT(email) > 1;

Drag options to blanks, or click blank then click option'

Aemail

B*

Attempts:

3 left

💡 Hint

Common Mistakes

Counting the email column inside COUNT() instead of '*'.

Grouping by a different column than selected.

Select the email column, count all rows with *, and group by email to find 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