UNIQUE constraint in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is the purpose of the UNIQUE constraint in SQL?

The UNIQUE constraint ensures that all values in a column or a group of columns are different from each other, preventing duplicate entries.

beginner

Can a table have multiple UNIQUE constraints?

Yes, a table can have multiple UNIQUE constraints on different columns or sets of columns to enforce uniqueness in multiple ways.

intermediate

How does UNIQUE constraint differ from PRIMARY KEY?

PRIMARY KEY uniquely identifies each row and does not allow NULLs. UNIQUE constraint also enforces uniqueness but allows NULL values (depending on the database).

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 it violates the UNIQUE constraint.

What does the UNIQUE constraint do in a SQL table?

A Allows duplicate values in a column

B Prevents duplicate values in a column

C Deletes duplicate rows automatically

D Creates an index without enforcing uniqueness

The UNIQUE constraint prevents duplicate values in the specified column(s).

Can a UNIQUE constraint column contain NULL values?

A No, NULLs are not allowed

B Yes, usually NULLs are allowed and can appear multiple times

C Yes, but only one NULL is allowed

D It depends on the database system

Whether NULLs are allowed and how many depends on the database system; some allow multiple NULLs, others allow only one.

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

A ALTER TABLE ... ADD UNIQUE

B CREATE UNIQUE CONSTRAINT

C INSERT UNIQUE INTO

D UPDATE TABLE ... SET UNIQUE

ALTER TABLE ... ADD UNIQUE is the correct syntax to add a UNIQUE constraint.

How many UNIQUE constraints can a table have?

A Only one

B Two

C Multiple

D None

A table can have multiple UNIQUE constraints on different columns or column sets.

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

A Constraint violation error

B Syntax error

C No error, duplicates allowed

D Connection error

Inserting a duplicate value violates the UNIQUE constraint and causes a constraint violation error.

Explain what the UNIQUE constraint does and how it differs from a PRIMARY KEY.

Think about uniqueness and NULL values.

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

Consider the SQL command and error behavior.