Now that we have created our users table, it’s time to move one step forward and build a photos table. This table is very important because this is where we will introdu
MSMuhammad SufiyanSoftware Engineer · 5d ago
Backend Engineering HubT-
Now that we have created our users table, it’s time to move one step forward and build a photos table.
This table is very important because this is where we will introduce our first real relationship between two tables using a foreign key.
Let’s walk through this step by step.
What Should the photos Table Contain?
A photo in our application needs three things:
1. id → uniquely identifies each photo 2. url → where the photo is stored 3. userId → which user owns this photo
So conceptually, our table looks like this:
Column
Purpose
id
Primary key
url
Photo location
userId
Owner of the photo
Step 1: Creating the photos Table
Just like the users table, we’ll start with an id column.
Why SERIAL Again?
We want PostgreSQL to auto-generate photo IDs
IDs must be unique
IDs must never change
So once again, we use SERIAL PRIMARY KEY.
Step 2: Adding the url Column
The url column will store a link to the photo.
We’re not actually storing real images, just fake URLs for learning.
url VARCHAR(200)
Why 200?
URLs can be long
Better to allow extra space than cause errors later
Step 3: Adding the Foreign Key (userId)
This is the most important part.
Each photo belongs to one user, so we need to store which user uploaded it.
Important Rule 🚨
We do NOT useSERIAL for foreign keys.
Why?
Because we don’t want PostgreSQL to generate random user IDs.
We want to explicitly say which user owns the photo.
So we use:
user_id INTEGER
Step 4: Turning user_id into a Foreign Key
This is where the magic happens ✨
We tell PostgreSQL:
“The values inside user_id must match values from the users.id column.”
That’s done using REFERENCES.
Final CREATE TABLE Query
CREATE TABLE photos (
id SERIAL PRIMARY KEY,
url VARCHAR(200),
user_id INTEGER REFERENCES users(id)
);
What Does REFERENCES users(id) Mean?
It means:
user_id can only contain values that exist inusers.id
You cannot attach a photo to a user that doesn’t exist
PostgreSQL enforces data consistency for us
This is a huge deal in real applications.
Step 5: Inserting a Photo
Now let’s insert a photo.
Remember:
We do NOT provide id
PostgreSQL handles it automatically
INSERT INTO photos (url, user_id)
VALUES ('http://img1.jpeg', 4);
