Foreign Keys – FastAPI - Beginner to Pro

📘 What Is a Foreign Key?

A Foreign Key (FK) is a column in one table that:

References the Primary Key (PK) of another table.

It creates a relationship between two tables.

In relational databases:

Primary keys uniquely identify records.
Foreign keys reference those primary keys to establish structured relationships.

Without foreign keys, tables are isolated.

With foreign keys, tables become connected.

🏗️ Our Project Structure

We have two tables:

Users Table

id (PK)	email	username	first_name	last_name	hashed_password	is_active
1	codeveda@gmail.com	codeveda1	code	veda	13ieidq80hscd…	1
2	example@gmail.com	example	example	user	uqwb38223jd…	1

Here:

id is the Primary Key
It uniquely identifies each user

Todos Table (With Foreign Key)

id (PK)	title	description	priority	owner (FK)
1	Go to store	To pickup eggs.	4	1
2	Haircut	Need to get length 1mm.	3	1
3	Feed dog	Make sure to use new food brand.	5	2
4	Water Plant	inside and Outside.	4	2
5	Learn something New	Learn to program.	5	1
6	Shower	You have not showered in 14 days.	5	2

Here:

owner is the Foreign Key
It references users.id

🧠 What This Actually Means

When owner = 1, the database understands:

This todo belongs to the user whose primary key is 1.

When owner = 2, it belongs to user 2.

This is how the relationship is enforced.

It is not just “a number.”

It is a reference pointer to another record.

🔎 Viewing Data With SQL

If we run:

SELECT * FROM todos;

We get all rows and columns from the todos table, including the foreign key column (owner).

This allows us to see:

What todos exist
Who owns each todo

If we run:

SELECT * FROM users;

We get all user records.

Notice something important:

The users table does not need a foreign key.

Why?

Because this is a one-to-many relationship:

The “many” side (todos) holds the foreign key.
The “one” side (users) does not.

This is how relational databases are designed.

🔁 How Do We Reference Users From Todos?

In our FastAPI application, once a user logs in:

We know their user ID.
That ID travels with each request (via authentication).

So when a user requests their todos:

We do not need to query the users table first.

We already have the user’s primary key.

So we can directly filter todos by that ID.

🧾 Using a WHERE Clause

Let’s say user 1 is logged in.

We can run:

SELECT * FROM todos WHERE owner = 1;

What this means:

Select all columns
From the todos table
Where the owner (foreign key) equals 1

This returns:

id	title	owner
1	Go to store	1
2	Haircut	1
5	Learn something New	1

Only that user’s todos.

For user 2:

SELECT * FROM todos WHERE owner = 2;

Returns:

id	title	owner
3	Feed dog	2
4	Water Plant	2
6	Shower	2

One to Many Relationship