Database Indexing easy
What is the relationship between a 'Unique Constraint' and an 'Index' in most databases?
- A They are conceptually unrelated; the constraint focuses on business logic, while the index focuses purely on retrieval speed.
- B Unique constraints are primarily enforced by middle-tier application filters rather than the database's own storage engine.
- C The database assumes that all incoming data is unique by default and only triggers a validation scan during off-peak hours.
- D A Unique Constraint typically triggers the creation of an underlying Unique Index to enforce the rule with O(log N) efficiency.
Answer: D. A Unique Constraint typically triggers the creation of an underlying Unique Index to enforce the rule with O(log N) efficiency.
To enforce a rule like 'No two users can have the same email', the database must check for existing duplicates on every single insert. Scanning the entire table for a match would be prohibitively slow (O(N)). Consequently, when you define a `UNIQUE` constraint, the database automatically creates a B-Tree index to perform this check instantly. This is why Unique constraints provide a performance boost for lookups as a secondary benefit.
One system design question a day, with spaced repetition so the ones you miss come back.
Get the appRelated questions
- Z-Order Curves (Geohashing) map 2D data to 1D strings for indexing. What is their primary weakness?
- In-Memory databases (Hyper, HANA) often favor Adaptive Radix Trees (ART). Why?
- Log-Structured Merge (LSM) Trees (RocksDB, Cassandra) optimize for Write Throughput. How does their disk IO pattern differ from B-Trees?
- In LSM compaction strategies, 'Universal' (Tiered) compaction prioritizes what over 'Leveled' compaction?
- In LSM trees, what is 'Space Amplification'?
- Why is it generally ineffective to index a 'Low Cardinality' column like `Gender` ('Male'/'Female')?