Primary key vs unique constraint vs index — what’s the difference?

Deep dive

These are related but not the same:

• **Primary key (PK)**: the table’s chosen row identifier. It implies **uniqueness** and typically **NOT NULL**. You can have only one PK per table, and other tables commonly reference it via foreign keys.
• **Unique constraint**: a rule that a column (or set of columns) must be unique. You can have many unique constraints per table and they often represent business identifiers (email, externalId, etc.).
• **Index**: a physical data structure (often a B-tree) that makes reads faster (lookups, joins, ordering). Indexes can be unique or non-unique.

Important nuance

In many databases, PK and unique constraints are enforced using an underlying **unique index**, but the *constraint* is the semantic rule and the *index* is the access path the query planner can use.

Practical tips

• Use PK for stable identity (surrogate id is common).
• Use unique constraints for business rules (e.g., `(tenantId, email)` unique).
• Add indexes to match query patterns (filters + join columns + ordering).

Common pitfalls

• Adding indexes “just in case” (slower writes, more storage).
• Forgetting composite uniqueness in multi-tenant schemas.
• Assuming a constraint automatically optimizes all queries (index design still matters).