Bird
Raised Fist0
SQLquery~5 mins

Unique index behavior in SQL - Time & Space Complexity

Choose your learning style10 modes available
LearnWhyDeepVisualTryChallengeProjectRecallTime
Time Complexity: Unique index behavior
O(log n)
Understanding Time Complexity

When we use a unique index in a database, it helps quickly find if a value already exists.

We want to know how the time to check or insert changes as the data grows.

Scenario Under Consideration

Analyze the time complexity of the following SQL commands involving a unique index.


CREATE UNIQUE INDEX idx_email ON users(email);

INSERT INTO users (email, name) VALUES ('a@example.com', 'Alice');

SELECT * FROM users WHERE email = 'a@example.com';

This code creates a unique index on the email column, inserts a new user, and searches by email.

Identify Repeating Operations

Look at what repeats when we insert or search using the unique index.

  • Primary operation: Searching the index tree to find the right spot or value.
  • How many times: The search steps grow slowly as data grows, not checking every row.
How Execution Grows With Input

As the number of users grows, the steps to find or insert an email grow slowly.

Input Size (n)Approx. Operations
10About 3 steps
100About 7 steps
1000About 10 steps

Pattern observation: The steps grow slowly, adding only a few more as data grows much larger.

Final Time Complexity

Time Complexity: O(log n)

This means the time to find or insert grows slowly, even if the data gets very big.

Common Mistake

[X] Wrong: "Checking a unique index takes as long as scanning all rows."

[OK] Correct: The unique index uses a tree structure that jumps quickly to the right place, so it does not check every row.

Interview Connect

Understanding unique index time helps you explain how databases keep searches fast even with lots of data.

Self-Check

What if we removed the unique index and only had a regular index? How would the time complexity change?

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