UNIQUE constraint in SQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the result of inserting duplicate values in a UNIQUE column?

Consider a table Users with a UNIQUE constraint on the email column. What happens if you try to insert two rows with the same email?

SQL

CREATE TABLE Users (
  id INT PRIMARY KEY,
  email VARCHAR(255) UNIQUE
);

INSERT INTO Users (id, email) VALUES (1, '[e-mail]');
INSERT INTO Users (id, email) VALUES (2, '[e-mail]');

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

B The database ignores the second insert silently.

C The second insert overwrites the first row.

D Both inserts succeed, allowing duplicate emails.

💡 Hint

Think about what UNIQUE means for a column in a database table.

The UNIQUE constraint prevents duplicate values in the specified column. Trying to insert a duplicate value causes an error.

🧠 Conceptual

intermediate

Which statement about UNIQUE constraints is true?

Choose the correct statement about UNIQUE constraints in SQL.

A A UNIQUE constraint allows duplicate values but no NULLs.

B A UNIQUE constraint automatically creates a primary key.

C A UNIQUE constraint allows multiple NULL values in the column.

D A UNIQUE constraint can only be applied to numeric columns.

💡 Hint

Think about how NULL values are treated in UNIQUE columns.

Most SQL databases allow multiple NULLs in a UNIQUE column because NULL means unknown, so duplicates are not considered.

📝 Syntax

advanced

Identify the correct syntax to add a UNIQUE constraint to an existing table column

You have a table Employees with a column phone_number. Which SQL statement correctly adds a UNIQUE constraint to this column?

A ALTER TABLE Employees ADD UNIQUE (phone_number);

B ALTER TABLE Employees ADD CONSTRAINT UNIQUE phone_number;

C ALTER TABLE Employees MODIFY phone_number UNIQUE;

D ALTER TABLE Employees ADD CONSTRAINT unique_phone UNIQUE (phone_number);

💡 Hint

Adding a named UNIQUE constraint requires a constraint name and column list.

The correct syntax uses ADD CONSTRAINT with a name and specifies the column(s) in parentheses.

❓ optimization

advanced

How does a UNIQUE constraint affect query performance?

Which of the following is a true effect of adding a UNIQUE constraint on a column in terms of query performance?

A It slows down all queries because of extra checks.

B It creates an index that can speed up searches on that column.

C It disables indexing on that column.

D It has no effect on query performance.

💡 Hint

Think about how databases enforce uniqueness internally.

Most databases create a unique index to enforce UNIQUE constraints, which also speeds up lookups on that column.

🔧 Debug

expert

Why does this UNIQUE constraint fail on multiple NULLs in this database?

In this database, a UNIQUE constraint on column code causes an error when inserting multiple rows with NULL in code. Why?

CREATE TABLE Products (
  id INT PRIMARY KEY,
  code VARCHAR(10) UNIQUE
);

INSERT INTO Products (id, code) VALUES (1, NULL);
INSERT INTO Products (id, code) VALUES (2, NULL);

A The database treats NULLs as equal, so duplicates violate UNIQUE.

B The UNIQUE constraint is not enforced on NULL values.

C The table lacks a primary key, causing the error.

D The code column must be NOT NULL to have a UNIQUE constraint.

💡 Hint

Some databases handle NULLs differently in UNIQUE constraints.

Some database systems treat NULL as a value that must be unique, so multiple NULLs violate UNIQUE constraints.

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