UNIQUE constraints in MySQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What happens when inserting duplicate values in a UNIQUE column?

Consider a table users with a UNIQUE constraint on the email column. What will be the result of this query?

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

MySQL

CREATE TABLE users (id INT PRIMARY KEY, email VARCHAR(255) UNIQUE);
INSERT INTO users (id, email) VALUES (1, '[e-mail]'), (2, '[e-mail]');

A The second row insertion fails with a duplicate key error.

B The entire insert statement fails and no rows are inserted.

C Both rows insert successfully because the UNIQUE constraint allows duplicates.

D Only the first row inserts; the second row is ignored without error.

💡 Hint

UNIQUE constraints prevent duplicate values in the specified column.

The UNIQUE constraint on the email column prevents duplicate emails. In a multi-row INSERT, if any row violates the constraint, the entire statement fails with a duplicate key error, and no rows are inserted.

🧠 Conceptual

intermediate

How does UNIQUE constraint differ from PRIMARY KEY?

Which statement correctly describes the difference between a UNIQUE constraint and a PRIMARY KEY constraint in MySQL?

A PRIMARY KEY uniquely identifies rows and cannot be NULL; UNIQUE allows multiple NULLs.

B UNIQUE is faster than PRIMARY KEY for indexing.

C PRIMARY KEY allows duplicates; UNIQUE does not.

D UNIQUE constraints automatically create foreign keys.

💡 Hint

Think about NULL values and uniqueness.

PRIMARY KEY columns must be unique and cannot contain NULL values. UNIQUE constraints enforce uniqueness but allow multiple NULLs because NULL is not considered equal to another NULL.

📝 Syntax

advanced

Which statement correctly adds a UNIQUE constraint to an existing column?

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

A ALTER TABLE employees MODIFY phone_number UNIQUE;

B ALTER TABLE employees ADD CONSTRAINT UNIQUE phone_number;

C ALTER TABLE employees ADD UNIQUE (phone_number);

D ALTER TABLE employees ADD UNIQUE phone_number;

💡 Hint

Look for the correct syntax to add a UNIQUE constraint with ALTER TABLE.

The correct syntax to add a UNIQUE constraint on a column is: ALTER TABLE table_name ADD UNIQUE (column_name); Options B, C, and D are syntactically incorrect.

❓ optimization

advanced

What is a performance impact of UNIQUE constraints on large tables?

Which statement best describes the performance impact of UNIQUE constraints on large tables during data insertion?

A UNIQUE constraints slow down inserts because the database must check for duplicates.

B UNIQUE constraints speed up inserts by indexing the column.

C UNIQUE constraints have no impact on insert performance.

D UNIQUE constraints cause inserts to fail silently if duplicates exist.

💡 Hint

Think about what the database must do to enforce uniqueness.

When inserting data, the database checks the UNIQUE constraint by searching the index to ensure no duplicates exist. This check adds overhead and slows down inserts compared to columns without constraints.

🔧 Debug

expert

Why does this UNIQUE constraint fail to prevent duplicates?

Given this table definition:

CREATE TABLE orders (order_id INT PRIMARY KEY, customer_id INT, order_date DATE, UNIQUE (customer_id));

Why might duplicates still appear in customer_id after inserting multiple rows?

A Because order_date allows duplicates, which affects customer_id uniqueness.

B Because UNIQUE constraints only apply to PRIMARY KEY columns.

C Because the UNIQUE constraint was not created properly without a name.

D Because NULL values in customer_id are allowed and can appear multiple times.

💡 Hint

Consider how UNIQUE constraints treat NULL values.

UNIQUE constraints allow multiple NULL values in the constrained column because NULL is not considered equal to another NULL. If customer_id is NULL in multiple rows, duplicates appear despite the UNIQUE constraint.