Creating Relationships in SQL: Auto Generated Id's
Enough theory for now. Let’s get our hands dirty and actually write some SQL. Up till now, we’ve talked a lot about relationships, primary keys, and foreign keys. But none of
MSMuhammad SufiyanSoftware Engineer · 5d ago
Backend Engineering HubT-
Enough theory for now.
Let’s get our hands dirty and actually write some SQL.
Up till now, we’ve talked a lot about relationships, primary keys, and foreign keys. But none of that really clicks until we see real tables and real data.
So in this blog, we’re going to:
Create our first real table (users)
Add a primary key
Let PostgreSQL automatically generate IDs
Insert data and verify everything works
This is the foundation of building relationships in SQL.
Step 1: Starting With a Clean Database
We’re using [sql.com](http://sql.com)/ PostgreSQL playground.
Earlier, we created a cities table. We don’t need it anymore, so we reset the database to start with a clean slate.
Resetting is optional, but it helps avoid confusion when learning.
Step 2: What Should the users Table Contain?
For now, our users table needs only two columns:
1. id → uniquely identifies each user 2. username → stores the user’s name
So logically, our table looks like this:
Column Name
Purpose
id
Primary key (unique identifier)
username
Name of the user
Step 3: Understanding SERIAL (Very Important)
If we make id just an INTEGER, then we would have to:
Manually generate IDs
Ensure they’re unique
Increment them correctly
That’s annoying 😅
So PostgreSQL gives us a helper type called SERIAL.
What does SERIAL do?
Automatically generates numbers
Starts from 1
Increments by 1 for every new row
Guarantees uniqueness
So Postgres handles IDs for us. Love that.
Step 4: Creating the users Table
Here’s the SQL to create the table:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50)
);
Let’s break this down 👇
CREATE TABLE users → creates a table named users
id SERIAL → auto-generated number
PRIMARY KEY → uniquely identifies each row
username VARCHAR(50) → string up to 50 characters
When we run this query → table created successfully ✅
Step 5: Inserting Users (Without IDs!)
Now comes the cool part.
Since id is auto-generated, we do NOT insert it manually.
We only insert usernames.
INSERT INTO users (username)
VALUES
('monahan93'),
('pfeiffer'),
('anus93'),
('strowman99');
What’s happening here?
We insert multiple users at once
PostgreSQL automatically assigns IDs
No manual ID handling needed
Step 6: Verifying the Result
Let’s fetch all users:
SELECT * FROM users;
Output looks like this 👇
id
username
1
monahan93
2
pfeiffer
3
anus93
4
strowman99
🎉 Success!
Key Observations (Very Important)
Every user got a unique ID
IDs were auto-generated
IDs will never change
We should never manually update IDs
So:
id = 1 → always belongs to monahan93
id = 2 → always belongs to pfeiffer
This stability is critical for relationships.
Why This Matters for Relationships
Soon, we’ll create a photos table.
Each photo will need to know:
“Which user owns me?”
That’s where this id becomes a foreign key in another table.
Example (coming next 👀):
photo_id
url
user_id
1
img1.jpg
4
This means:
“This photo belongs to user with id = 4”
Final Summary
✔ Every table must have a primary key
✔ SERIAL makes life easier
✔ PostgreSQL auto-generates IDs
✔ IDs are stable and unique
✔ These IDs will be used as foreign keys later
HIRINGMINE CAREER SIGNAL
This writing is proof of expertise.
Explore the author’s verified skills, projects and availability—or start a professional conversation.
