UNIQUE constraint in SQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to add a UNIQUE constraint on the email column.

SQL

CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(255) [1]);

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

AUNIQUE

BPRIMARY KEY

CNOT NULL

DCHECK

Attempts:

3 left

💡 Hint

Common Mistakes

Using PRIMARY KEY instead of UNIQUE for email column.

Forgetting to add any constraint, allowing duplicates.

The UNIQUE constraint ensures that all values in the email column are different.

2fill in blank

easy

Complete the code to add a UNIQUE constraint on the code column.

SQL

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

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

ACHECK

BUNIQUE

CPRIMARY KEY

DNOT NULL

Attempts:

3 left

💡 Hint

Common Mistakes

Using PRIMARY KEY instead of UNIQUE for code column.

Forgetting to add any constraint, allowing duplicates.

The UNIQUE constraint ensures that all values in the code column are different.

3fill in blank

medium

Complete the code to add a UNIQUE constraint to the username column after table creation.

SQL

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

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

Aunique_username

Bcheck_username

Cpk_username

Dfk_username

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'pk_' prefix which is for primary keys.

Using 'fk_' which is for foreign keys.

You must name the UNIQUE constraint. 'unique_username' is a good descriptive name.

4fill in blank

hard

Fill both blanks to create a UNIQUE constraint on two columns: first_name and last_name.

SQL

ALTER TABLE employees ADD CONSTRAINT [1] [2] (first_name, last_name);

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

Aunique_fullname

BPRIMARY KEY

CUNIQUE

DCHECK

Attempts:

3 left

💡 Hint

Common Mistakes

Using PRIMARY KEY instead of UNIQUE for multiple columns.

Not naming the constraint.

The constraint name is 'unique_fullname' and the type is UNIQUE to ensure the combination of first_name and last_name is unique.

5fill in blank

hard

Fill all three blanks to create a table with a UNIQUE constraint on the email column and a PRIMARY KEY on id.

SQL

CREATE TABLE customers ([1] INT [2], email VARCHAR(255) [3]);

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

Aid

BPRIMARY KEY

CUNIQUE

DNOT NULL

Attempts:

3 left

💡 Hint

Common Mistakes

Forgetting to add PRIMARY KEY to id.

Not adding UNIQUE to email.

The id column is defined as PRIMARY KEY, and the email column has a UNIQUE constraint to prevent duplicate emails.

Practice

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

easy

A. To automatically generate unique IDs

B. To allow NULL values in a column

C. To speed up query performance

D. To prevent duplicate values in a column or group of columns

Solution

1. Step 1: Understand the UNIQUE constraint and compare to options

   The UNIQUE constraint ensures that all values in a column or a set of columns are different from each other. The other options describe different SQL features, but only "To prevent duplicate values in a column or group of columns" correctly describes the UNIQUE constraint.

2. Final Answer:

   To prevent duplicate values in a column or group of columns -> Option D

3. Quick Check:

   UNIQUE constraint = prevent duplicates [OK]

Hint: UNIQUE means no two rows can have same value [OK]

Common Mistakes:

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

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 ADD UNIQUE email;

B. ALTER TABLE users ADD CONSTRAINT UNIQUE email;

C. ALTER TABLE users ADD UNIQUE (email);

D. ALTER TABLE users ADD UNIQUE CONSTRAINT (email);

Solution

1. Step 1: Recall ALTER TABLE syntax for UNIQUE and check options

   The correct syntax to add a UNIQUE constraint is: ALTER TABLE table_name ADD UNIQUE (column_name); "ALTER TABLE users ADD UNIQUE (email);" matches exactly. The other options misuse keywords or omit parentheses.

2. Final Answer:

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

3. Quick Check:

   ALTER TABLE ADD UNIQUE (column) syntax = ALTER TABLE users ADD UNIQUE (email); [OK]

Hint: Use ADD UNIQUE (column) with parentheses [OK]

Common Mistakes:

• Omitting parentheses around column name
• Using ADD CONSTRAINT UNIQUE without naming constraint
• Misplacing keywords in ALTER TABLE

3. Given the table products with a UNIQUE constraint on product_code, what happens when you run this SQL?

INSERT INTO products (product_code, name) VALUES ('X123', 'Item A');
INSERT INTO products (product_code, name) VALUES ('X123', 'Item B');

medium

A. The second insert fails with a UNIQUE constraint violation error

B. The second insert overwrites the first row

C. Both rows are inserted successfully

D. The database ignores the second insert silently

Solution

1. Step 1: Analyze UNIQUE constraint effect on the insert statements

   The UNIQUE constraint on product_code means no two rows can have the same product_code value. The first insert adds 'X123' successfully. The second insert tries to add the same product_code 'X123', which violates the UNIQUE constraint, causing an error.

2. Final Answer:

   The second insert fails with a UNIQUE constraint violation error -> Option A

3. Quick Check:

   Duplicate insert on UNIQUE column = error [OK]

Hint: Duplicate UNIQUE values cause insert errors [OK]

Common Mistakes:

• Assuming duplicates overwrite existing rows
• Thinking duplicates are ignored silently
• Believing both inserts succeed

4. You tried to add a UNIQUE constraint on column username but got an error. What is the most likely cause?

medium

A. The column username is not indexed

B. The column username contains duplicate values already

C. UNIQUE constraints cannot be added after table creation

D. The table does not exist

Solution

1. Step 1: Understand UNIQUE addition failure and evaluate causes

   Adding a UNIQUE constraint fails if existing data violates uniqueness (duplicates exist). "The column username contains duplicate values already" identifies the cause. A non-existent table is unrelated (assuming it exists); UNIQUE can be added later; indexing is irrelevant or automatic.

2. Final Answer:

   The column username contains duplicate values already -> Option B

3. Quick Check:

   Duplicates prevent adding UNIQUE constraint [OK]

Hint: Check for duplicates before adding UNIQUE [OK]

Common Mistakes:

• Assuming UNIQUE can't be added after creation
• Ignoring existing duplicate data
• Confusing indexing with UNIQUE constraint

5. You want to ensure that the combination of first_name and last_name in the employees table is unique, but duplicates are allowed in each column individually. Which SQL statement correctly enforces this?

hard

A. ALTER TABLE employees ADD UNIQUE (first_name, last_name);

B. ALTER TABLE employees ADD UNIQUE (first_name); ALTER TABLE employees ADD UNIQUE (last_name);

C. ALTER TABLE employees ADD UNIQUE first_name, last_name;

D. ALTER TABLE employees ADD UNIQUE CONSTRAINT employees_unique (first_name);

Solution

1. Step 1: Understand multi-column UNIQUE and analyze options

   A UNIQUE constraint on multiple columns ensures the combination is unique (individual duplicates allowed). "ALTER TABLE employees ADD UNIQUE (first_name, last_name);" correctly adds UNIQUE on the pair. Adding UNIQUE separately disallows individual duplicates; other syntax is wrong or ignores one column.

2. Final Answer:

   ALTER TABLE employees ADD UNIQUE (first_name, last_name); -> Option A

3. Quick Check:

   Multi-column UNIQUE = unique pairs [OK]

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

Common Mistakes:

• Adding UNIQUE on each column separately
• Using incorrect syntax without parentheses
• Ignoring the need for combined uniqueness