Unique indexes in MySQL - 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.

MySQL

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

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

Aidx_users_email

Bidx_email_unique

Cemail_index

Dunique_email

Attempts:

3 left

💡 Hint

Common Mistakes

Using a name that is already used by another index.

Forgetting to specify UNIQUE keyword (though here it's part of the statement).

The index name can be any valid identifier. Here, idx_email_unique clearly indicates a unique index on the email column.

2fill in blank

medium

Complete the code to add a unique constraint on the 'username' column in the 'accounts' table.

MySQL

ALTER TABLE accounts ADD CONSTRAINT [1] UNIQUE (username);

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

Ausername_unique

Busername_key

Cunique_username

Dunique_key

Attempts:

3 left

💡 Hint

Common Mistakes

Using a generic name like 'unique_key' which is not descriptive.

Using a name that conflicts with existing constraints.

The constraint name username_unique clearly indicates a unique constraint on the username column.

3fill in blank

hard

Fix the error in the code to create a unique index on 'phone_number' in the 'contacts' table.

MySQL

CREATE [1] INDEX idx_phone ON contacts(phone_number);

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

AFULLTEXT

BPRIMARY

CFOREIGN

DUNIQUE

Attempts:

3 left

💡 Hint

Common Mistakes

Using PRIMARY or FOREIGN which are for different constraints.

Omitting the UNIQUE keyword.

The keyword UNIQUE is required to create a unique index. Other options are for different index types.

4fill in blank

hard

Fill both blanks to create a unique index named 'idx_unique_code' on the 'code' column of the 'products' table.

MySQL

CREATE [1] INDEX [2] ON products(code);

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

AUNIQUE

Bidx_code

Cidx_unique_code

DPRIMARY

Attempts:

3 left

💡 Hint

Common Mistakes

Using PRIMARY instead of UNIQUE.

Using a different index name than requested.

The keyword UNIQUE creates a unique index, and the index name should be idx_unique_code as requested.

5fill in blank

hard

Fill all three blanks to add a unique constraint named 'uc_email' on the 'email' column of the 'subscribers' table.

MySQL

ALTER TABLE [1] ADD CONSTRAINT [2] [3] (email);

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

Asubscribers

Buc_email

CUNIQUE

Dusers

Attempts:

3 left

💡 Hint

Common Mistakes

Using the wrong table name.

Using a generic constraint name.

Omitting the UNIQUE keyword.

The table is subscribers, the constraint name is uc_email, and the keyword UNIQUE enforces uniqueness.

Practice

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

easy

A. To prevent duplicate values in one or more columns

B. To speed up data insertion

C. To allow duplicate values in a column

D. To delete duplicate rows automatically

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 are incorrect or unrelated.

3. Final Answer:

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

4. Quick Check:

   UNIQUE index = prevent duplicates [OK]

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

Common Mistakes:

• Thinking UNIQUE speeds up inserts
• Believing UNIQUE allows duplicates
• Confusing UNIQUE with automatic deletion

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

easy

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

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

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

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

Solution

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

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

2. Step 2: Match options with correct syntax

   CREATE UNIQUE INDEX idx_email ON users(email); matches exactly. Others have wrong keyword order or syntax errors.

3. Final Answer:

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

4. Quick Check:

   CREATE UNIQUE INDEX ... ON ... (column) [OK]

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

Common Mistakes:

• Swapping UNIQUE and INDEX keywords
• Using FROM instead of ON
• Incorrect keyword order

3. Given the table employees with a UNIQUE index on employee_id, what happens if you run this query?

INSERT INTO employees (employee_id, name) VALUES (101, 'Alice');
INSERT INTO employees (employee_id, name) VALUES (101, 'Bob');

medium

A. The second insert fails with a duplicate key error

B. Both rows are inserted successfully

C. The second insert overwrites the first row

D. The second insert is ignored silently

Solution

1. Step 1: Understand UNIQUE index effect on inserts

   A UNIQUE index on employee_id prevents duplicate values in that column.

2. Step 2: Analyze the inserts

   The first insert adds employee_id 101. The second insert tries to add the same employee_id 101, violating the UNIQUE constraint, causing an error.

3. Final Answer:

   The second insert fails with a duplicate key error -> Option A

4. Quick Check:

   UNIQUE index blocks duplicate inserts [OK]

Hint: Duplicate insert on UNIQUE column causes error [OK]

Common Mistakes:

• Assuming duplicates are allowed
• Thinking second insert overwrites first
• Believing second insert is ignored silently

4. You tried to create a UNIQUE index on the column username but got an error. Which of these is the most likely cause?

medium

A. The table does not exist

B. The username column contains duplicate values

C. You forgot to specify the index name

D. UNIQUE indexes are not supported on text columns

Solution

1. Step 1: Understand UNIQUE index creation requirements

   Creating a UNIQUE index requires all existing values in the column to be unique.

2. Step 2: Identify common error causes

   If duplicates exist, MySQL throws an error when creating the UNIQUE index.

3. Final Answer:

   The username column contains duplicate values -> Option B

4. Quick Check:

   Duplicates block UNIQUE index creation [OK]

Hint: Check for duplicates before creating UNIQUE index [OK]

Common Mistakes:

• Assuming index name is mandatory
• Believing UNIQUE not allowed on text columns
• Ignoring existing duplicates

5. You want to ensure that the combination of first_name and last_name in the contacts table is unique, but duplicates are still appearing. Which is the best way to fix this?

hard

A. Use a CHECK constraint to prevent duplicates

B. Create separate UNIQUE indexes on first_name and last_name individually

C. Add a PRIMARY KEY on first_name

D. Create a UNIQUE index on both first_name and last_name columns together

Solution

1. Step 1: Understand uniqueness on multiple columns

   To ensure uniqueness on a combination, a composite UNIQUE index on both columns is needed.

2. Step 2: Analyze other options

   Separate UNIQUE indexes allow duplicates across combinations. PRIMARY KEY on one column doesn't cover both. CHECK constraints can't enforce uniqueness.

3. Final Answer:

   Create a UNIQUE index on both first_name and last_name columns together -> Option D

4. Quick Check:

   Composite UNIQUE index = unique pairs [OK]

Hint: Use composite UNIQUE index for multi-column uniqueness [OK]

Common Mistakes:

• Creating separate UNIQUE indexes instead of composite
• Using PRIMARY KEY on one column only
• Relying on CHECK constraints for uniqueness