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.