Relationships: Primary Keys and Foreign Keys
Welcome back.
In the previous lesson, you learned how to filter and sort data.
You used:
WHERE;- comparison operators;
AND;OR;LIKE;ILIKE;ORDER BY;LIMIT;OFFSET.
Very useful.
Very SQL.
Today we move to one of the most important ideas in relational databases:
Relationships.
PostgreSQL is a relational database.
That means tables can be connected.
A table does not need to store everything alone like a lonely spreadsheet in a dark folder.
Instead, we can split data into clean tables and connect them using keys.
This is where PostgreSQL becomes serious.
Still friendly.
But serious.
What You Will Learn
In this lesson, you will learn:
- what a primary key is;
- what a foreign key is;
- why relationships matter;
- how tables can reference other tables;
- what one-to-many relationships are;
- how to create related tables;
- how to insert related data;
- what happens when a foreign key points to missing data;
- why duplicated data is dangerous;
- how PostgreSQL protects relationships;
- how to prepare for
JOIN.
By the end of this lesson, you will understand how tables can work together.
This is a major step.
One table is useful.
Connected tables are powerful.
Like tools in a workshop.
One screwdriver is nice.
A full toolbox is better.
Unless you drop it on your foot.
The Problem with One Big Table
Imagine we want to store products and categories.
We could create one table like this:
id | product_name | category_name
---|--------------|---------------
1 | Laptop | Electronics
2 | Mouse | Electronics
3 | Desk Chair | Furniture
4 | Bookshelf | Furniture
This works.
At first.
But there is duplication.
The word Electronics appears again and again.
The word Furniture appears again and again.
Now imagine there are 10,000 products.
If you need to rename Electronics to Electronic Devices, you must update many rows.
That is annoying.
And dangerous.
Because one row may become:
Electronic Devices
Another may stay:
Electronics
Another may become:
Eletronics
Congratulations.
You now have three categories.
And one spelling injury.
A Better Structure
Instead of storing category names repeatedly, we can create two tables:
categories
products
The categories table stores categories once.
The products table stores products and references a category.
Example:
categories
id | name
---|-------------
1 | Electronics
2 | Furniture
products
id | name | category_id
---|------------|------------
1 | Laptop | 1
2 | Mouse | 1
3 | Desk Chair | 2
4 | Bookshelf | 2
Now products do not store the category name directly.
They store category_id.
This connects each product to a category.
Cleaner.
Safer.
Less repetition.
Less chaos.
The database breathes better.
Probably.
Primary Key
A primary key uniquely identifies each row in a table.
Example:
id SERIAL PRIMARY KEY
In this table:
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
The id column is the primary key.
That means each category has a unique ID.
Example:
id | name
---|-------------
1 | Electronics
2 | Furniture
The primary key helps PostgreSQL identify each row clearly.
No confusion.
No “which Electronics do you mean?”
Just:
category id 1
Very direct.
Very database.
Foreign Key
A foreign key is a column that points to a primary key in another table.
Example:
category_id INTEGER REFERENCES categories(id)
This means:
category_id must refer to an existing id in the categories table.
Example:
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
category_id INTEGER REFERENCES categories(id)
);
Here:
products.idis the primary key of theproductstable;products.category_idis a foreign key;- it points to
categories.id.
This creates a relationship.
PostgreSQL now knows:
Each product can belong to a category.
Very useful.
The tables are now talking.
Politely.
For now.
One-to-Many Relationship
A one-to-many relationship means:
One row in one table can be connected to many rows in another table.
Example:
One category can have many products.
Electronics can have:
- Laptop;
- Mouse;
- Keyboard;
- Monitor;
- USB Cable.
Furniture can have:
- Desk Chair;
- Bookshelf;
- Desk Lamp.
So:
categories -> products
one category -> many products
This is one of the most common relationships in databases.
Other examples:
one customer -> many orders
one author -> many books
one teacher -> many courses
one user -> many posts
If you build real applications, you will see one-to-many relationships everywhere.
They are like screws in furniture.
Small.
Important.
Often ignored until something breaks.
Prepare the Database
Open PostgreSQL:
sudo -iu postgres psql
Connect to your database:
\c learning_postgresql
If you do not have this database, create it:
CREATE DATABASE learning_postgresql;
Then connect:
\c learning_postgresql
Now remove old tables if they exist.
Important:
Drop products first because it will depend on categories.
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;
Order matters.
If one table depends on another, PostgreSQL may not allow you to delete the parent table first.
PostgreSQL protects relationships.
Like a strict parent.
But for data.
Create the Categories Table
Create the categories table:
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name VARCHAR(100) UNIQUE NOT NULL
);
This table has:
id;name.
The id is the primary key.
The name is required and unique.
That means we cannot have two categories with the same name.
Good.
No duplicate Electronics.
No Electronics, electronics, and Electronicss party.
Well, PostgreSQL can still treat different spelling as different values.
But UNIQUE protects exact duplicates.
The database is strong.
Not magical.
Insert Categories
Insert categories:
INSERT INTO categories (name)
VALUES
('Electronics'),
('Furniture'),
('Office');
Check the table:
SELECT * FROM categories;
You should see:
1 | Electronics
2 | Furniture
3 | Office
The IDs may be different if you already inserted data before.
That is fine.
Do not panic.
PostgreSQL counts.
Sometimes it remembers old numbers even after deletes.
Databases remember more than humans expect.
Slightly scary.
Create the Products Table
Now create the products table:
CREATE TABLE products (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
price NUMERIC(10, 2) CHECK (price >= 0),
category_id INTEGER REFERENCES categories(id)
);
This table has:
id;name;price;category_id.
The important part is:
category_id INTEGER REFERENCES categories(id)
This creates the foreign key.
It means every category_id in products must match an existing id in categories.
PostgreSQL will protect this rule.
Because PostgreSQL is not here for imaginary categories.
Insert Products with Category IDs
Insert products:
INSERT INTO products (name, price, category_id)
VALUES
('Laptop', 900.00, 1),
('Mouse', 25.00, 1),
('Desk Chair', 150.00, 2),
('Notebook', 5.00, 3),
('Pen', 2.00, 3);
Now check:
SELECT * FROM products;
You should see products with category_id.
Example:
id | name | price | category_id
---|------------|--------|------------
1 | Laptop | 900.00 | 1
2 | Mouse | 25.00 | 1
3 | Desk Chair | 150.00 | 2
4 | Notebook | 5.00 | 3
5 | Pen | 2.00 | 3
This is good.
But it is not very readable yet.
We see category_id.
We do not see category names.
Soon we will learn JOIN.
For now, understand the relationship.
The product knows which category it belongs to.
Through the ID.
Very relational.
Very PostgreSQL.
Try a Wrong Foreign Key
Now try inserting a product with a category that does not exist:
INSERT INTO products (name, price, category_id)
VALUES ('Mystery Device', 99.00, 999);
PostgreSQL should reject it.
Why?
Because there is no category with id = 999.
This is the foreign key doing its job.
Without the foreign key, PostgreSQL would accept the row.
Then you would have a product pointing to nothing.
A ghost category.
A sad little orphan row.
Foreign keys prevent that.
They protect relationships.
They say:
You cannot point to something that does not exist.
Very strict.
Very useful.
Why Foreign Keys Matter
Foreign keys protect your data.
They prevent:
- products with missing categories;
- orders with missing customers;
- comments with missing posts;
- payments with missing invoices;
- enrollments with missing students.
Without foreign keys, your database can become inconsistent.
Example:
Product says category_id = 999
But category 999 does not exist
That is bad.
The application may crash.
The report may be wrong.
The developer may start drinking too much coffee.
Foreign keys reduce this kind of pain.
Not all pain.
But some.
And that matters.
Parent and Child Tables
In a relationship, we often say:
parent table
child table
In our example:
categories = parent table
products = child table
Why?
Because products depend on categories.
A product can reference a category.
So:
categories.id
is the parent key.
And:
products.category_id
is the foreign key.
Another example:
customers = parent table
orders = child table
One customer can have many orders.
Each order references one customer.
This vocabulary appears often.
Do not fear it.
Parent table.
Child table.
Database family.
Less emotional than real family.
Usually.
Inspect the Tables
Use:
\d categories
Then:
\d products
In the products table, PostgreSQL should show the foreign key constraint.
You may see something like:
Foreign-key constraints:
"products_category_id_fkey" FOREIGN KEY (category_id) REFERENCES categories(id)
The name may be generated automatically.
It may look ugly.
That is normal.
PostgreSQL names constraints like a robot naming children.
Functional.
Not poetic.
Delete Problem: Parent Row in Use
Try deleting a category that has products:
DELETE FROM categories
WHERE id = 1;
PostgreSQL may reject it.
Why?
Because products still reference category 1.
If PostgreSQL allowed the delete, products would point to a missing category.
Again, orphan rows.
PostgreSQL says:
No. Fix the children first.
Very parental.
Very database.
To delete the category, you would first need to update or delete related products.
Example:
DELETE FROM products
WHERE category_id = 1;
Then:
DELETE FROM categories
WHERE id = 1;
Be careful with deletes.
Always use SELECT first.
You know the rule.
Helmet on.
ON DELETE Behavior
Foreign keys can define what happens when a parent row is deleted.
Common options include:
ON DELETE RESTRICT;ON DELETE CASCADE;ON DELETE SET NULL.
By default, PostgreSQL usually prevents deleting parent rows that are still referenced.
That is safe.
ON DELETE CASCADE means:
If the parent is deleted, delete related child rows too.
Example:
category_id INTEGER REFERENCES categories(id) ON DELETE CASCADE
Be careful.
CASCADE is powerful.
Sometimes useful.
Sometimes dangerous.
It can delete many related rows automatically.
Like dominoes.
But with data.
In beginner projects, use it only when you really understand the consequence.
The database will not say:
Are you emotionally sure?
It will just do it.
Avoid Repeated Data
Relationships help avoid duplicated data.
Bad design:
products
id | name | category_name
---|--------|--------------
1 | Laptop | Electronics
2 | Mouse | Electronics
Better design:
categories
id | name
---|-------------
1 | Electronics
products
id | name | category_id
---|--------|------------
1 | Laptop | 1
2 | Mouse | 1
Now the category name exists in one place.
This is cleaner.
If the category name changes, update one row.
Not 500 rows.
This is one of the reasons relational databases exist.
Less duplication.
Better consistency.
Fewer spelling disasters.
Another Example: Students and Courses
Imagine a learning platform.
A course can have many students.
A student can also join many courses.
That is a many-to-many relationship.
Many-to-many relationships need an extra table.
Example:
students
courses
enrollments
The enrollments table connects students and courses.
We will learn this more deeply later.
For now, remember:
- one-to-many uses one foreign key;
- many-to-many usually uses a connection table.
Do not panic.
This is normal.
Databases like structure.
Sometimes a lot of structure.
Like a very organized librarian.
Simple One-to-Many Example: Authors and Books
Let us create another simple relationship.
One author can have many books.
First remove old tables if needed:
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;
Create authors:
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
Create books:
CREATE TABLE books (
id SERIAL PRIMARY KEY,
title VARCHAR(150) NOT NULL,
author_id INTEGER REFERENCES authors(id)
);
Insert authors:
INSERT INTO authors (name)
VALUES
('George Orwell'),
('Jane Austen');
Insert books:
INSERT INTO books (title, author_id)
VALUES
('1984', 1),
('Animal Farm', 1),
('Pride and Prejudice', 2);
Check:
SELECT * FROM authors;
SELECT * FROM books;
Now one author can have many books.
This is one-to-many.
Simple.
Powerful.
Classic.
Like tea.
But SQL.
Common Mistakes
Creating the Child Table First
Wrong order:
CREATE TABLE products (
id SERIAL PRIMARY KEY,
category_id INTEGER REFERENCES categories(id)
);
If categories does not exist yet, PostgreSQL cannot create the foreign key.
Create the parent table first.
Then the child table.
Correct order:
1. categories
2. products
Parents first.
Children later.
Database family planning.
Inserting Child Rows Before Parent Rows
Wrong:
INSERT INTO products (name, price, category_id)
VALUES ('Laptop', 900.00, 1);
If category 1 does not exist, PostgreSQL rejects it.
Correct:
1. Insert categories
2. Insert products
The referenced row must exist first.
You cannot point to a chair that is not there.
Unless you are doing philosophy.
This is SQL.
Using Names Instead of IDs
Bad idea:
product stores category_name directly
Better:
product stores category_id
Names can change.
IDs should stay stable.
A category name may change from Office to Office Supplies.
But its ID can remain the same.
IDs are boring.
That is why they are useful.
Ignoring Foreign Keys
You can build tables without foreign keys.
But then PostgreSQL cannot protect relationships.
That means your application must do all the work.
And applications are written by humans.
Humans forget things.
Foreign keys are database seatbelts.
Use them.
Practice
Create two tables:
customers
orders
One customer can have many orders.
Create customers:
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(150) UNIQUE NOT NULL
);
Create orders:
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
order_date DATE DEFAULT CURRENT_DATE,
total NUMERIC(10, 2) CHECK (total >= 0),
customer_id INTEGER REFERENCES customers(id)
);
Insert customers:
INSERT INTO customers (name, email)
VALUES
('Anna', 'anna@example.com'),
('Marco', 'marco@example.com');
Insert orders:
INSERT INTO orders (total, customer_id)
VALUES
(49.99, 1),
(120.00, 1),
(35.50, 2);
Check:
SELECT * FROM customers;
SELECT * FROM orders;
Then try inserting an order with a missing customer:
INSERT INTO orders (total, customer_id)
VALUES (99.00, 999);
PostgreSQL should reject it.
Good.
That is the foreign key protecting your data.
Mini Challenge
Create a small database structure for blog posts.
You need two tables:
authors
posts
Rules:
- one author can have many posts;
- each post belongs to one author;
- author name is required;
- post title is required;
- post content can be long text;
- post published status defaults to
false.
Suggested structure:
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title VARCHAR(150) NOT NULL,
content TEXT,
published BOOLEAN DEFAULT false,
author_id INTEGER REFERENCES authors(id)
);
Insert at least two authors.
Insert at least three posts.
Then run:
SELECT * FROM authors;
SELECT * FROM posts;
Try inserting a post with author_id = 999.
PostgreSQL should reject it.
That is not PostgreSQL being annoying.
That is PostgreSQL guarding your data like a serious librarian with a keyboard.
Summary
Today you learned:
- a primary key uniquely identifies a row;
- a foreign key points to a primary key in another table;
- relationships connect tables;
- one-to-many means one row can relate to many rows;
- parent tables are referenced by child tables;
- child tables often contain foreign keys;
- foreign keys prevent invalid references;
- PostgreSQL can reject rows that point to missing data;
- relationships reduce duplicated data;
- table creation order matters;
- insert order matters;
ON DELETEcontrols what happens when parent rows are deleted.
This is a huge step.
You are now moving from isolated tables to relational database design.
That is what PostgreSQL is made for.
Tables are not lonely islands.
They can connect.
They can reference.
They can protect each other.
Very beautiful.
In a database way.
Next Lesson
In the next lesson, we will learn JOIN.
This is where relationships become visible in query results.
Right now, products show category_id.
Useful, but not very friendly.
With JOIN, we will show:
Laptop | Electronics
Mouse | Electronics
Chair | Furniture
That is where connected tables start to feel powerful.
And much more readable.