UNIQUE constraints in MySQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to add a UNIQUE constraint to the 'email' column in the 'users' table.

MySQL

ALTER TABLE users ADD CONSTRAINT [1] UNIQUE (email);

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

Aprimary_key

Bunique_email

Cindex_email

Dforeign_key

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'primary_key' as the constraint name instead of a unique constraint name.

Using 'index_email' which is an index, not a constraint.

The UNIQUE constraint requires a name, here 'unique_email' is a valid constraint name.

2fill in blank

medium

Complete the code to create a table 'products' with a UNIQUE constraint on the 'sku' column.

MySQL

CREATE TABLE products (id INT PRIMARY KEY, sku VARCHAR(50) [1] UNIQUE);

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

ANOT NULL

BNULL

CDEFAULT

DAUTO_INCREMENT

Attempts:

3 left

💡 Hint

Common Mistakes

Using NULL which allows multiple NULLs and breaks uniqueness.

Using AUTO_INCREMENT on a VARCHAR column.

The 'sku' column should be NOT NULL to enforce uniqueness properly.

3fill in blank

hard

Fix the error in the code to add a UNIQUE constraint on 'username' in the 'accounts' table.

MySQL

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

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

Aunique_username

Busername

Cusername_unique

Dusername_key

Attempts:

3 left

💡 Hint

Common Mistakes

Using the column name 'username' as the constraint name causes confusion.

Using names that are not descriptive of the constraint purpose.

The constraint name should be a valid identifier; 'unique_username' is clear and valid.

4fill in blank

hard

Fill both blanks to create a UNIQUE constraint on 'email' and 'phone' columns in 'contacts' table.

MySQL

ALTER TABLE contacts ADD CONSTRAINT [1] UNIQUE ([2]);

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

Aunique_contact

Bemail, phone

Cemail,phone

Dcontact_unique

Attempts:

3 left

💡 Hint

Common Mistakes

Using spaces after commas in column list causes syntax errors.

Using generic constraint names that don't describe the constraint.

The constraint name 'unique_contact' is valid, and columns must be listed without spaces after commas.

5fill in blank

hard

Fill all three blanks to create a table 'employees' with a UNIQUE constraint on 'email' and 'employee_id'.

MySQL

CREATE TABLE employees (id INT PRIMARY KEY, [1] VARCHAR(100) NOT NULL, [2] INT NOT NULL, CONSTRAINT [3] UNIQUE (email, employee_id));

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

Aemail

Bemployee_id

Cunique_emp

Demp_email

Attempts:

3 left

💡 Hint

Common Mistakes

Mixing up column names or missing NOT NULL on unique columns.

Using constraint names that are not descriptive.

'email' and 'employee_id' columns are defined, and 'unique_emp' is the constraint name.

Practice

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

easy

A. To prevent duplicate values in a column or set of columns

B. To allow NULL values in a column

C. To speed up query performance

D. To automatically generate unique IDs

Solution

1. Step 1: Understand the role of UNIQUE constraint

   A UNIQUE constraint ensures that all values in a column or a group of columns are different from each other.

2. Step 2: Compare with other options

   Options A, C, and D describe other features: allowing NULLs, indexing, and auto-increment, which are not the main purpose of UNIQUE.

3. Final Answer:

   To prevent duplicate values in a column or set of columns -> Option A

4. Quick Check:

   UNIQUE constraint = prevent duplicates [OK]

Hint: UNIQUE means no two rows share the same value [OK]

Common Mistakes:

• Confusing UNIQUE with PRIMARY KEY
• Thinking UNIQUE allows duplicates
• Assuming UNIQUE speeds up queries

2. Which of the following is the correct syntax to add a UNIQUE constraint to an existing column email in a table users?

easy

A. ALTER TABLE users SET UNIQUE email;

B. ALTER TABLE users ADD UNIQUE (email);

C. ALTER TABLE users ADD UNIQUE email;

D. ALTER TABLE users ADD CONSTRAINT UNIQUE email;

Solution

1. Step 1: Recall correct ALTER TABLE syntax for UNIQUE

   The correct syntax is to use ADD UNIQUE (column_name) inside ALTER TABLE.

2. Step 2: Check each option

   ALTER TABLE users ADD UNIQUE (email); matches the correct syntax. Options A, B, and C are invalid or incomplete syntax.

3. Final Answer:

   ALTER TABLE users ADD UNIQUE (email); -> Option B

4. Quick Check:

   ALTER TABLE ADD UNIQUE (column) [OK]

Hint: Use ADD UNIQUE (column) to add constraint [OK]

Common Mistakes:

• Omitting parentheses around column name
• Using SET UNIQUE which is invalid
• Missing ADD keyword

3. Given the table users(id INT, username VARCHAR(50), email VARCHAR(100)) with a UNIQUE constraint on email, what happens if you run this query twice?

INSERT INTO users (id, username, email) VALUES (1, 'alice', '[e-mail]');

medium

A. Both inserts succeed, creating two identical rows

B. Both inserts fail due to UNIQUE constraint

C. Second insert updates the existing row

D. First insert succeeds, second insert fails with duplicate error

Solution

1. Step 1: Understand UNIQUE constraint effect on inserts

   The UNIQUE constraint on email prevents duplicate emails. The first insert adds the row successfully.

2. Step 2: Analyze second insert behavior

   The second insert tries to add the same email again, violating the UNIQUE constraint, causing an error.

3. Final Answer:

   First insert succeeds, second insert fails with duplicate error -> Option D

4. Quick Check:

   UNIQUE blocks duplicate inserts [OK]

Hint: Duplicate insert fails due to UNIQUE constraint [OK]

Common Mistakes:

• Assuming duplicates are allowed
• Thinking second insert updates existing row
• Confusing UNIQUE with PRIMARY KEY behavior

4. You have this table creation statement:

CREATE TABLE products (id INT, code VARCHAR(20) UNIQUE);

You try to insert two rows:

INSERT INTO products VALUES (1, 'X123');
INSERT INTO products VALUES (2, 'X123');

But the second insert fails. What is the most likely cause?

medium

A. The UNIQUE constraint on code prevents duplicate values

B. The id column is missing a PRIMARY KEY

C. The table does not allow NULL values in code

D. The syntax of the INSERT statement is incorrect

Solution

1. Step 1: Identify the UNIQUE constraint effect

   The code column has a UNIQUE constraint, so duplicate values like 'X123' are not allowed.

2. Step 2: Analyze the insert failure reason

   The second insert tries to add 'X123' again, violating the UNIQUE constraint, causing failure.

3. Final Answer:

   The UNIQUE constraint on code prevents duplicate values -> Option A

4. Quick Check:

   UNIQUE constraint blocks duplicate column values [OK]

Hint: UNIQUE blocks duplicate values in that column [OK]

Common Mistakes:

• Thinking PRIMARY KEY is required for uniqueness
• Assuming NULL disallows duplicates
• Blaming syntax error for insert failure

5. You want to ensure that the combination of first_name and last_name in a contacts table is unique, but each column alone can have duplicates. Which is the correct way to enforce this in MySQL?

hard

A. Add a PRIMARY KEY on first_name

B. Add UNIQUE constraints separately on first_name and last_name

C. Add a UNIQUE constraint on (first_name, last_name) together

D. Add a UNIQUE constraint only on last_name

Solution

1. Step 1: Understand composite UNIQUE constraints

   A UNIQUE constraint on multiple columns ensures the combination is unique, but allows duplicates in each column individually.

2. Step 2: Evaluate options for enforcing uniqueness

   Add a UNIQUE constraint on (first_name, last_name) together correctly adds a UNIQUE constraint on the pair (first_name, last_name). Options B, C, and D enforce uniqueness incorrectly or partially.

3. Final Answer:

   Add a UNIQUE constraint on (first_name, last_name) together -> Option C

4. Quick Check:

   Composite UNIQUE = unique pairs, not individual columns [OK]

Hint: Use UNIQUE on multiple columns for combined uniqueness [OK]

Common Mistakes:

• Adding UNIQUE on each column separately
• Confusing PRIMARY KEY with UNIQUE
• Not using composite constraint for pairs