UNIQUE constraints in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a UNIQUE constraint in a database?

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

beginner

Can a table have multiple UNIQUE constraints?

Yes, a table can have multiple UNIQUE constraints on different columns or sets of columns to ensure uniqueness in each specified area.

intermediate

How does a UNIQUE constraint differ from a PRIMARY KEY?

A PRIMARY KEY uniquely identifies each row and cannot be NULL. A UNIQUE constraint also enforces uniqueness but allows NULL values (unless specified otherwise).

beginner

Write a SQL statement to add a UNIQUE constraint on the 'email' column of a table named 'users'.

ALTER TABLE users ADD CONSTRAINT unique_email UNIQUE (email);

beginner

What happens if you try to insert a duplicate value into a column with a UNIQUE constraint?

The database will reject the insert or update operation and return an error because the UNIQUE constraint is violated.

What does a UNIQUE constraint do in a database table?

A Deletes duplicate rows automatically

B Allows duplicate values but no NULLs

C Automatically creates an index without enforcing uniqueness

D Prevents duplicate values in a column or set of columns

A UNIQUE constraint ensures that all values in the specified column(s) are unique, preventing duplicates.

Can a UNIQUE constraint column contain NULL values?

A No, NULLs are not allowed

B Yes, NULLs are allowed and can appear multiple times

C Yes, but only one NULL is allowed

D It depends on the database system

In MySQL, UNIQUE columns can contain multiple NULL values because NULL is not considered equal to another NULL.

Which SQL command adds a UNIQUE constraint to an existing table?

A ALTER TABLE table_name ADD UNIQUE (column_name);

B CREATE UNIQUE CONSTRAINT table_name (column_name);

C UPDATE TABLE table_name SET UNIQUE = TRUE;

D INSERT UNIQUE INTO table_name (column_name);

The correct syntax to add a UNIQUE constraint is ALTER TABLE with ADD UNIQUE on the column.

How many UNIQUE constraints can a table have?

A Only one

B Two

C Multiple on different columns or column sets

D None

A table can have multiple UNIQUE constraints on different columns or combinations of columns.

What error occurs if you insert a duplicate value into a UNIQUE constrained column?

A Duplicate entry error

B Syntax error

C No error, duplicates allowed

D Connection error

The database returns a duplicate entry error because the UNIQUE constraint is violated.

Explain what a UNIQUE constraint is and how it helps maintain data integrity.

Think about how UNIQUE constraints stop repeated data in a column.

Describe how to add a UNIQUE constraint to an existing table and what happens if a duplicate value is inserted.

Recall the SQL command and the database behavior on duplicates.