Unique indexes in MySQL - Step-by-Step Execution

Concept Flow - Unique indexes

Create Table

↓

Add Unique Index

↓

Insert Row

↓

Check Unique Constraint

↓

Insert OK

↓

Next Insert or End

When you create a unique index, the database checks each new row to make sure the indexed column values are unique. If a duplicate is found, the insert is rejected.

Execution Sample

MySQL

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

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

This code creates a table with a unique index on email, inserts one row, then tries to insert a duplicate email which fails.

Execution Table

Step	Action	Input Data	Unique Check Result	Outcome
1	Create table users with unique index on email	N/A	N/A	Table created
2	Insert row	id=1, email='[e-mail]'	No duplicate found	Row inserted
3	Insert row	id=2, email='[e-mail]'	Duplicate email found	Insert rejected

💡 Insert rejected at step 3 because email '[e-mail]' already exists, violating unique index

Variable Tracker

Variable	Start	After Step 2	After Step 3
users table rows	empty	[{id:1, email:'[e-mail]'}]	[{id:1, email:'[e-mail]'}] (no change)

Key Moments - 2 Insights

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

Because the unique index is on the email column, not the id. The email '[e-mail]' already exists, so the unique constraint rejects the insert (see execution_table step 3).

What happens if we insert a row with a different email?

The insert will succeed because the unique index only rejects duplicates on the indexed column. This is shown in execution_table step 2 where a new unique email is inserted.

Visual Quiz - 3 Questions

Look at the execution table, what is the outcome of step 2?

A Insert rejected due to duplicate

B Table creation failed

C Row inserted successfully

D No action taken

💡 Hint

Check the 'Outcome' column in execution_table row for step 2

At which step does the unique constraint cause an insert to be rejected?

A Step 2

B Step 3

C Step 1

D No rejection occurs

💡 Hint

Look at the 'Unique Check Result' and 'Outcome' columns in execution_table

If the unique index was removed, what would happen at step 3?

A Insert would succeed

B Insert would still be rejected

C Table would drop

D Database error unrelated to unique index

💡 Hint

Refer to the concept_flow where unique index enforces uniqueness

Concept Snapshot

Unique indexes ensure column values are unique in a table.
When inserting, the database checks for duplicates.
If a duplicate exists, the insert is rejected.
Useful for columns like email to avoid duplicates.
Syntax: UNIQUE (column_name) in CREATE TABLE or ALTER TABLE.

Full Transcript

Unique indexes in MySQL are special rules that make sure no two rows have the same value in certain columns. When you create a unique index on a column, like email, the database checks every new row you add. If the email already exists, it stops you from adding that row. For example, if you insert a user with email '[e-mail]' and then try to insert another user with the same email, the second insert will fail. This helps keep data clean and prevents duplicates. The unique index only cares about the columns it covers, so other columns like id can have duplicates if not indexed uniquely.