UNIQUE constraint in SQL - Step-by-Step Execution

Concept Flow - UNIQUE constraint

Start Table Creation

↓

Define Columns

↓

Apply UNIQUE Constraint

↓

Insert Data

↓

Check New Value Against Existing Values

↓

Reject Insert

↓

Continue

When you add a UNIQUE constraint to a column, the database checks every new value to make sure it doesn't already exist in that column. If it does, the insert is rejected.

Execution Sample

SQL

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

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

This code creates a table with a UNIQUE constraint on the email column, then tries to insert two rows with the same email.

Execution Table

Step	Action	Value Checked	Constraint Check Result	Insert Result
1	Create table Users with UNIQUE on email	N/A	N/A	Table created
2	Insert (1, '[e-mail]')	'[e-mail]'	No duplicate found	Insert successful
3	Insert (2, '[e-mail]')	'[e-mail]'	Duplicate found	Insert rejected

💡 Insert rejected at step 3 because '[e-mail]' already exists violating UNIQUE constraint

Variable Tracker

Variable	Start	After Step 2	After Step 3
Users.email	empty	['[e-mail]']	['[e-mail]'] (no change, insert rejected)

Key Moments - 2 Insights

Why was the second insert rejected even though the id was different?

Because the UNIQUE constraint applies to the email column, not the id. The email '[e-mail]' was already in the table (see execution_table step 3).

Does UNIQUE constraint allow NULL values?

Yes, UNIQUE allows multiple NULLs because NULL is treated as unknown and not equal to other NULLs. This is not shown in the current example but is important to know.

Visual Quiz - 3 Questions

Look at the execution table, what happens at step 3 when inserting a duplicate email?

A The insert is rejected due to UNIQUE constraint

B The insert is successful

C The table is dropped

D The duplicate email is updated

💡 Hint

Check the 'Insert Result' column at step 3 in the execution_table

According to variable_tracker, what is the state of Users.email after step 3?

A Contains two identical emails

B Is empty

C Contains one email, no change after step 3

D Contains NULL values

💡 Hint

Look at the 'After Step 3' column for Users.email in variable_tracker

If the UNIQUE constraint was removed, what would happen at step 3?

A Insert would be rejected

B Insert would succeed allowing duplicate emails

C Table would be dropped

D Error in table creation

💡 Hint

UNIQUE constraint enforces no duplicates; without it duplicates are allowed (see concept_flow)

Concept Snapshot

UNIQUE constraint ensures all values in a column are different.
It rejects inserts that duplicate existing values.
Allows multiple NULLs as NULLs are not considered equal.
Used to enforce data uniqueness without being a primary key.
Syntax example: column_name TYPE UNIQUE

Full Transcript

The UNIQUE constraint in SQL makes sure that every value in a column is different from all others. When you insert a new row, the database checks if the value already exists in that column. If it does, the insert is rejected. For example, if you have a Users table with a UNIQUE email column, inserting two rows with the same email will fail on the second insert. The UNIQUE constraint allows multiple NULL values because NULL is treated as unknown and not equal to other NULLs. This constraint helps keep data clean and prevents duplicates in important columns.

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