Compare Primary Keys, Primary Indexes, and Secondary Indexes

Quick Overview

Distinguish primary-key constraints from primary and secondary indexes, and assess storage cost, write amplification, selectivity, and composite-index order.

Compare Primary Keys, Primary Indexes, and Secondary Indexes

Company: Squarepoint

Role: Software Engineer

Category: Software Engineering Fundamentals

Difficulty: medium

Interview Round: Technical Screen

Explain the difference between a primary key, a primary index, and a secondary index. What are the disadvantages of adding indexes, and how would you decide whether an index is useful for a workload? ### Constraints & Assumptions Index terminology and physical storage differ by database engine. State the engine assumptions instead of treating a primary key as universally synonymous with clustered storage. ### Clarifying Questions to Ask - Which database engine and storage organization are in use? - Are the important operations point lookups, range scans, joins, or frequent writes? ```hint Separate a constraint from an access path Uniqueness and row identity are logical rules; an index is a structure used to find rows efficiently. ``` ### What a Strong Answer Covers - Primary-key uniqueness/non-null identity and its enforcement. - Engine-dependent primary or clustered indexing versus additional access paths. - Write amplification, storage, maintenance, and low-selectivity trade-offs. ### Follow-up Questions - Why can a table scan be cheaper than using a secondary index? - How does a composite index's column order affect range queries?

Overview: Distinguish primary-key constraints from primary and secondary indexes, and assess storage cost, write amplification, selectivity, and composite-index order.

Squarepoint logo
Squarepoint
Mar 15, 2026
mediumSoftware EngineerTechnical ScreenSoftware Engineering Fundamentals
0
0

Explain the difference between a primary key, a primary index, and a secondary index. What are the disadvantages of adding indexes, and how would you decide whether an index is useful for a workload?

Constraints & Assumptions

Index terminology and physical storage differ by database engine. State the engine assumptions instead of treating a primary key as universally synonymous with clustered storage.

Clarifying Questions to Ask Guidance

  • Which database engine and storage organization are in use?
  • Are the important operations point lookups, range scans, joins, or frequent writes?

What a Strong Answer Covers Guidance

  • Primary-key uniqueness/non-null identity and its enforcement.
  • Engine-dependent primary or clustered indexing versus additional access paths.
  • Write amplification, storage, maintenance, and low-selectivity trade-offs.

Follow-up Questions Guidance

  • Why can a table scan be cheaper than using a secondary index?
  • How does a composite index's column order affect range queries?