Bird
Raised Fist0
SQLquery~10 mins

Unique index behavior in SQL - Step-by-Step Execution

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
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.
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
StepActionEmail ValueIndex Check ResultInsert Result
1Create unique index on email--Index created
2Insert row with email 'a@example.com'a@example.comNot foundInsert successful
3Insert row with email 'b@example.com'b@example.comNot foundInsert successful
4Insert row with email 'a@example.com'a@example.comFound duplicateInsert rejected
💡 Insert rejected at step 4 because 'a@example.com' already exists in unique index
Variable Tracker
VariableStartAfter Step 2After Step 3After Step 4
users.emailempty['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'?
AInsert is rejected due to duplicate
BInsert is successful because email is unique
CIndex is dropped
DInsert is delayed
💡 Hint
Check execution_table row 3 under 'Insert Result' column
At which step does the unique index detect a duplicate email?
AStep 4
BStep 2
CStep 3
DNo duplicates detected
💡 Hint
Look at execution_table 'Index Check Result' column for duplicate detection
If the unique index was removed, what would happen at step 4?
AInsert would be rejected anyway
BDatabase would crash
CInsert would succeed allowing duplicate emails
DInsert would be delayed
💡 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.

Practice

(1/5)
1. What is the main purpose of a UNIQUE index in a database table?
easy
A. To store data in alphabetical order
B. To speed up data insertion
C. To allow duplicate values for faster queries
D. To prevent duplicate values in one or more columns

Solution

  1. Step 1: Understand the role of UNIQUE index

    A UNIQUE index ensures that no two rows have the same value in the indexed column(s).

  2. Step 2: Compare options with this definition

    Only To prevent duplicate values in one or more columns correctly states that UNIQUE prevents duplicates. Others describe unrelated behaviors.

  3. Final Answer:

    To prevent duplicate values in one or more columns -> Option D

  4. Quick Check:

    UNIQUE index = prevent duplicates [OK]
Hint: UNIQUE means no duplicates allowed in indexed columns [OK]
Common Mistakes:
  • Thinking UNIQUE speeds up insertion
  • Confusing UNIQUE with sorting
  • Believing UNIQUE allows duplicates
2. Which of the following is the correct SQL syntax to create a unique index on the column email in a table named users?
easy
A. CREATE UNIQUE KEY idx_email ON users(email);
B. CREATE UNIQUE INDEX idx_email ON users(email);
C. CREATE INDEX UNIQUE idx_email ON users(email);
D. CREATE UNIQUE TABLE idx_email ON users(email);

Solution

  1. Step 1: Recall correct syntax for unique index creation

    The standard syntax is: CREATE UNIQUE INDEX index_name ON table_name(column_name);

  2. Step 2: Match options to syntax

    CREATE UNIQUE INDEX idx_email ON users(email); matches exactly. CREATE INDEX UNIQUE idx_email ON users(email); has wrong keyword order. CREATE UNIQUE KEY idx_email ON users(email); uses UNIQUE KEY which is for constraints, not index creation. CREATE UNIQUE TABLE idx_email ON users(email); is invalid syntax.

  3. Final Answer:

    CREATE UNIQUE INDEX idx_email ON users(email); -> Option B

  4. Quick Check:

    CREATE UNIQUE INDEX = CREATE UNIQUE INDEX idx_email ON users(email); [OK]
Hint: Syntax: CREATE UNIQUE INDEX name ON table(column) [OK]
Common Mistakes:
  • Swapping UNIQUE and INDEX keywords
  • Using UNIQUE KEY instead of UNIQUE INDEX
  • Trying to create UNIQUE TABLE (invalid)
3. Given the table products with a unique index on sku, what happens when you run this SQL?
INSERT INTO products (sku, name) VALUES ('A123', 'Item1');
INSERT INTO products (sku, name) VALUES ('A123', 'Item2');
medium
A. Second insert fails with a duplicate key error
B. Both inserts fail due to unique index
C. First insert fails, second succeeds
D. Both rows insert successfully

Solution

  1. Step 1: Understand unique index effect on inserts

    The unique index on sku means no two rows can have the same sku value.

  2. Step 2: Analyze the insert statements

    The first insert adds 'A123' successfully. The second tries to insert 'A123' again, violating uniqueness, causing an error.

  3. Final Answer:

    Second insert fails with a duplicate key error -> Option A

  4. Quick Check:

    Duplicate insert = error [OK]
Hint: Duplicate value in UNIQUE column causes insert error [OK]
Common Mistakes:
  • Assuming duplicates are allowed
  • Thinking first insert fails
  • Believing both inserts fail
4. You have a unique index on the username column. The following query causes an error:
INSERT INTO users (username) VALUES ('john');
INSERT INTO users (username) VALUES ('john');

What is the best way to fix this error?
medium
A. Change the second insert to a different username
B. Remove the unique index on username
C. Insert NULL instead of 'john' in the second insert
D. Use a SELECT query instead of INSERT

Solution

  1. Step 1: Identify cause of error

    The unique index prevents duplicate usernames. Inserting 'john' twice violates this.

  2. Step 2: Choose a fix that respects uniqueness

    Changing the second insert to a different username avoids duplicates and keeps data integrity.

  3. Final Answer:

    Change the second insert to a different username -> Option A

  4. Quick Check:

    Fix duplicates by changing values [OK]
Hint: Change duplicate value to unique to fix insert error [OK]
Common Mistakes:
  • Removing unique index (loses data integrity)
  • Inserting NULL (may not be allowed or unique)
  • Using SELECT instead of INSERT (doesn't fix duplicates)
5. Consider a table employees with a unique index on (email, department_id). Which of these inserts will cause a unique index violation if the table already has a row with email='alice@example.com' and department_id=10?
hard
A. INSERT INTO employees (email, department_id) VALUES ('bob@example.com', 10);
B. INSERT INTO employees (email, department_id) VALUES ('alice@example.com', 11);
C. INSERT INTO employees (email, department_id) VALUES ('alice@example.com', 10);
D. INSERT INTO employees (email, department_id) VALUES ('bob@example.com', 11);

Solution

  1. Step 1: Understand composite unique index

    The unique index on (email, department_id) means the combination of both values must be unique.

  2. Step 2: Check each insert against existing row

    Only INSERT INTO employees (email, department_id) VALUES ('alice@example.com', 10); duplicates both email and department_id, causing violation. Others differ in at least one value, so allowed.

  3. Final Answer:

    INSERT with email='alice@example.com' and department_id=10 causes violation -> Option C

  4. Quick Check:

    Composite UNIQUE = unique pairs [OK]
Hint: Composite unique index blocks duplicate value pairs [OK]
Common Mistakes:
  • Thinking unique applies to each column separately
  • Assuming different department_id allows duplicate email
  • Ignoring composite key uniqueness
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard