UNIQUE constraints in MySQL - Step-by-Step Execution

Concept Flow - UNIQUE constraints

Start Table Creation

↓

Define Columns

↓

Add UNIQUE Constraint

↓

Insert Data

↓

Check for Duplicate Values

↓

Insert Row

↓

Continue or End

When creating a table, you define columns and add UNIQUE constraints to ensure no duplicate values in those columns. When inserting data, the database checks for duplicates and rejects rows that violate the UNIQUE rule.

Execution Sample

MySQL

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 users table with a UNIQUE constraint on the email column, then tries to insert two rows with the same email.

Execution Table

Step	Action	Data Inserted	Check UNIQUE Constraint	Result
1	Create table with UNIQUE on email	N/A	N/A	Table created
2	Insert row (1, '[e-mail]')	(1, '[e-mail]')	No duplicate found	Row inserted
3	Insert row (2, '[e-mail]')	(2, '[e-mail]')	Duplicate email found	Insert rejected with error

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

Variable Tracker

Variable	Start	After Step 2	After Step 3
users table rows	empty	[(1, '[e-mail]')]	[(1, '[e-mail]')] (no change, insert rejected)

Key Moments - 2 Insights

Why does the second insert fail even though the id is different?

Because the UNIQUE constraint is on the email column, not the id. The second insert tries to add a duplicate email, which violates the UNIQUE rule as shown in execution_table step 3.

Can a UNIQUE constraint allow NULL values?

Yes, UNIQUE constraints allow multiple NULLs because NULL means unknown, so duplicates are not considered. This is not shown in the example but is a common behavior in MySQL.

Visual Quiz - 3 Questions

Look at the execution table, what happens at step 2?

A The row is inserted because no duplicate email exists

B The insert is rejected due to duplicate email

C The table is dropped

D The UNIQUE constraint is removed

💡 Hint

Check the 'Result' column in execution_table row 2

At which step does the UNIQUE constraint cause an error?

A Step 2

B Step 3

C Step 1

D No error occurs

💡 Hint

Look at the 'Check UNIQUE Constraint' and 'Result' columns in execution_table

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

A Table would be dropped

B Insert would still fail

C Insert would succeed, duplicate emails allowed

D Insert would cause syntax error

💡 Hint

UNIQUE constraints prevent duplicates; removing it allows duplicates as per concept_flow

Concept Snapshot

UNIQUE constraints ensure column values are unique in a table.
Defined during table creation or altered later.
Inserts violating UNIQUE cause errors.
Allows multiple NULLs in MySQL.
Used to prevent duplicate data entries.

Full Transcript

This visual execution shows how UNIQUE constraints work in MySQL. First, a table is created with a UNIQUE constraint on the email column. When inserting the first row with email '[e-mail]', it succeeds because the table is empty. When inserting a second row with the same email, the database checks the UNIQUE constraint, finds a duplicate, and rejects the insert with an error. The variable tracker shows the table rows after each step, confirming no change after the failed insert. Key moments clarify why the second insert fails despite different ids and that UNIQUE allows multiple NULLs. The quiz tests understanding of when inserts succeed or fail due to UNIQUE constraints. The snapshot summarizes the main points for quick review.