Concept Flow - Unique index behavior
Create Unique Index
Insert Row
Check if Value Exists in Index
Reject Insert
Next Insert or End
This flow shows how a unique index checks for duplicates before allowing data insertion.
0
0
Unique index behavior in SQL - Step-by-Step Execution
Execution Sample
SQL
CREATE UNIQUE INDEX idx_email ON users(email); INSERT INTO users (id, email) VALUES (1, 'a@example.com'); INSERT INTO users (id, email) VALUES (2, 'b@example.com'); INSERT INTO users (id, email) VALUES (3, 'a@example.com');
Creates a unique index on email and tries inserting rows, showing rejection of duplicate email.
Execution Table
| Step | Action | Email Value | Index Check Result | Insert Result |
|---|---|---|---|---|
| 1 | Create unique index on email | - | - | Index created |
| 2 | Insert row with email 'a@example.com' | a@example.com | Not found | Insert successful |
| 3 | Insert row with email 'b@example.com' | b@example.com | Not found | Insert successful |
| 4 | Insert row with email 'a@example.com' | a@example.com | Found duplicate | Insert rejected |
💡 Insert rejected at step 4 because 'a@example.com' already exists in unique index
Variable Tracker
| Variable | Start | After Step 2 | After Step 3 | After Step 4 |
|---|---|---|---|---|
| users.email | empty | ['a@example.com'] | ['a@example.com', 'b@example.com'] | ['a@example.com', 'b@example.com'] |
Key Moments - 2 Insights
Why was the third insert rejected even though it looks like a new row?
Because the unique index on 'email' found that 'a@example.com' already exists (see execution_table step 4), so it rejects duplicates to keep emails unique.
Does the unique index allow multiple rows with the same email?
No, the unique index prevents duplicate values in the indexed column, so only unique emails can be inserted (see variable_tracker showing no change after step 4).
Visual Quiz - 3 Questions
Test your understanding
Look at the execution table, what happens at step 3 when inserting 'b@example.com'?
💡 Hint
Check execution_table row 3 under 'Insert Result' column
At which step does the unique index detect a duplicate email?
💡 Hint
Look at execution_table 'Index Check Result' column for duplicate detection
If the unique index was removed, what would happen at step 4?
💡 Hint
Unique index enforces uniqueness; without it duplicates can be inserted
Concept Snapshot
Unique index ensures column values are unique. Before inserting, it checks if value exists. If duplicate found, insert is rejected. Helps maintain data integrity. Useful for columns like email or username.
Full Transcript
This visual execution shows how a unique index works in a database. First, a unique index is created on the email column. When inserting a new row, the database checks if the email already exists in the index. If it does not exist, the insert succeeds and the email is added to the index. If the email already exists, the insert is rejected to prevent duplicates. This ensures that all emails in the table are unique, maintaining data integrity.