Keys and Relationships

Last Updated: June 12, 2026 • 20 min read

1. Introduction

Keys are the mechanism that gives relational databases their power. They uniquely identify rows within tables and establish connections between tables, enabling the complex multi-table queries that make SQL so valuable.

2. Why It Matters

Key design and relationship modeling are core system design interview topics. Getting them wrong leads to data integrity violations, orphaned records, and queries that return incorrect results. Interviewers evaluate your ability to choose appropriate primary keys and model relationships correctly (one-to-many, many-to-many, one-to-one).

Data integrity: Primary keys prevent duplicate rows; foreign keys prevent orphaned references.
Query performance: Proper key design enables efficient JOINs and index usage.
Schema evolution: Choosing surrogate vs. natural keys affects how easily your schema adapts to business changes.

3. Real-World Analogy

Think of keys like social security numbers and family relationships. Your SSN (primary key) uniquely identifies you. Your parent's SSN on your birth certificate is a foreign key linking you to them. A family tree is a network of these relationships — one parent can have many children (one-to-many), and married couples link two family trees (many-to-many through a marriage junction).

4. How It Works

Three key types work together:

Primary Key: A column (or combination) that uniquely identifies each row. Enforces uniqueness and non-null. Types: surrogate (auto-increment integer, UUID), natural (email, ISBN), composite (multiple columns).
Foreign Key: A column referencing another table's primary key. Enforces referential integrity — the FK value must match an existing PK value or be NULL. Actions: RESTRICT, CASCADE, SET NULL, SET DEFAULT, NO ACTION.
Unique Key: Ensures no duplicate values in a column. Unlike PK, allows NULL and you can have multiple unique keys per table.

5. Internal Architecture

When you define a primary key, PostgreSQL automatically creates a B-tree index on the PK column(s). Foreign key constraints are enforced by checking the referenced table's index on every INSERT/UPDATE/DELETE. This is why PK/FK columns should be indexed — without an index on the FK side, DELETE operations on the parent table must scan the entire child table.

6. Visual Explanation

7. Practical Example

Complete schema demonstrating all relationship types:

8. Common Mistakes

Common Pitfall

Storing comma-separated foreign key values in a TEXT column (e.g., course_ids = "1,2,3") instead of using a junction table. This violates First Normal Form and makes JOINs impossible.

Interview Insight

When designing schemas in interviews, default to surrogate keys (SERIAL or UUID). Mention adding a UNIQUE constraint on the natural key (email, username) for business logic enforcement. This shows you understand both performance and data integrity.

Using mutable natural keys as PK: Email addresses can change. Use surrogate keys and add UNIQUE on natural keys.
Forgetting ON DELETE rules: Default RESTRICT causes "foreign key constraint violation" errors when deleting parent records with children.
Missing FK indexes: Without an index on the FK column, DELETE on the parent table becomes a full table scan on the child.

9. Quick Quiz

Q1: What's the difference between RESTRICT and CASCADE on DELETE?

RESTRICT prevents deletion of the parent row if child rows exist. CASCADE automatically deletes the child rows when the parent is deleted.

Q2: How do you model a many-to-many relationship?

Create a junction table with foreign keys to both parent tables. The junction table's primary key is typically a composite of both FKs.

10. Scenario-Based Challenge

Scenario

You're building a library system. Books can have multiple authors, and authors can write multiple books. Design the schema. What happens if an author is deleted from the system — should their books be deleted too? What ON DELETE action makes sense?

11. Debugging Exercise

This schema has a relationship bug. Can you find it?

Fix: Add ON DELETE CASCADE or ON DELETE SET NULL to the foreign key, depending on whether orders should be deleted or orphaned when a customer is removed.

12. Interview Questions

Q: When would you choose a UUID over SERIAL for a primary key?

UUIDs are preferred when IDs must be globally unique (distributed systems, client-side generation, microservices). SERIAL is simpler and faster for single-database applications but leaks information (insertion order, volume).

Q: What is a self-referencing foreign key?

A FK that references the same table's PK. Used for hierarchical data: employees with managers, categories with parent categories, comments with parent comments.

Q: Can a table have multiple foreign keys? Multiple unique keys?

Yes to both. A table can have many foreign keys (referencing different tables) and many unique keys. But it can have only one primary key.

13. Production Considerations

Index FK columns: Always create an index on foreign key columns. Without it, DELETE operations on parent tables require full table scans on children.
CASCADE carefully: ON DELETE CASCADE can cause unexpected data loss in deep relationship chains. Use RESTRICT for critical data and handle deletions explicitly in application code.
UUID performance: UUIDs are 16 bytes vs 4 bytes for INTEGER. At scale, this increases index size and slows JOINs. Consider ULID or sequential UUIDs for better index locality.
Composite PK trade-offs: Composite primary keys (e.g., in junction tables) create wider indexes. For very high-throughput tables, consider a surrogate PK + UNIQUE constraint on the composite.