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.

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