Primary Keys & Foreign Keys

How Databases Actually Store Relationships

Up until now, we’ve talked a lot about relationships in databases:

• One-to-Many

• Many-to-One

• One-to-One

• Many-to-Many

But there’s one big missing piece:

👉 How do we actually store these relationships in a database?
👉 How does SQL connect rows across different tables?

This is where Primary Keys and Foreign Keys come in.

---

Why Do We Even Need Keys?

So far in the course, we’ve created tables like cities and phones, but we haven’t added anything that uniquely identifies a row.

In real-world databases, this is not optional.

📌 Every table must be able to uniquely identify each row
📌 Every relationship depends on this uniqueness

That’s exactly what keys are for.

---

Primary Key (PK)

A Primary Key is a column that uniquely identifies one single row in a table.

Key Properties of a Primary Key

A primary key:

• Is unique

• Never repeats

• Never changes

• Identifies exactly one row

Example: Photos Table

Here:

• id is the primary key

• No two rows can ever have the same id

• If we ask for photo with id = 2, we will always get exactly one photo

Even if rows move around or sorting changes, the ID stays the same.

💡 Think of a primary key like a CNIC number or roll number — unique and permanent.

---

Every Table Has a Primary Key

From now on, assume this rule:

✅ Every table we create will have a primary key

Examples:

• users.id

• photos.id

• comments.id

• likes.id

Without a primary key, relationships become unreliable and messy.

---

Foreign Key (FK)

A Foreign Key is how tables connect to each other.

If a primary key identifies a row inside its own table,
a foreign key points to a row in another table.

---

One-to-Many Relationship (User → Photos)

We already learned:

A user has many photos

Now let’s store that in data.

Users Table

Here:

• id is the primary key

---

Photos Table (with Foreign Key)

Here:

• id → primary key

• user_id → foreign key

📌 user_id stores the ID of the user who owns the photo

---

How the Relationship Works

• Photos with user_id = 4 belong to Instagram

• Photos with user_id = 1 belong to monahan93

So when we say:

“These photos belong to this user”

What we actually mean is:

“These photos have a user_id that matches the user’s primary key”

---

Querying the Relationship

Get all photos for user with id = 4

SELECT *
FROM photos
WHERE user_id = 4;

This query works only because of the foreign key.

---

One-to-Many vs Many-to-One (Same Data!)

Important thing to understand:

• From users → photos → One-to-Many

• From photos → user → Many-to-One

Same tables.
Same data.
Different perspective.

💡 The database structure does not change — only how you think about it does.

---

Why Foreign Keys Are So Powerful

Foreign keys allow us to:

• Link tables together

• Maintain data consistency

• Write JOIN queries

• Represent real-world ownership

Without foreign keys:

• Relationships would exist only in your head

• SQL would have no way to connect records

---

Mental Model (Very Important)

Think like this:

• Primary Key → “Who am I?”

• Foreign Key → “Who do I belong to?”

Example:

• users.id → Who is this user?

• photos.user_id → Who owns this photo?

---

Summary

✔ Every table has a primary key
✔ Primary keys uniquely identify rows
✔ Foreign keys store another table’s primary key
✔ One-to-Many relationships are implemented using foreign keys
✔ Many-to-One is the same relationship, just viewed differently

---

What’s Next?

At this point:

• You know all four relationship types

• You know how relationships are stored

• You understand PKs and FKs conceptually

👉 Next, we’ll look at more examples and then start writing real SQL queries using relationships and JOINs.

That’s where everything really starts to click 🔥