#1 Data Analytics Program in India
₹2,499₹1,499Enroll Now
Step 8
10 min read

Foreign Key (Old)

Learn how Foreign Keys connect tables together - explained with simple, everyday examples.

What You Will Learn

Customers Table
customer_id (PRIMARY KEY)nameemail
1Johnjohn@email.com
2Marymary@email.com
2 rows
Orders Table (customer_id is FOREIGN KEY)
order_id (PRIMARY KEY)customer_id (FOREIGN KEY)total
101150
102175
1032100
3 rows
  • What a Foreign Key is (in very simple words)
  • How it connects tables
  • Why we use it
  • Easy real-life examples

What is a Foreign Key?

Customers Table
customer_id (PRIMARY KEY)nameemail
1Johnjohn@email.com
2Marymary@email.com
2 rows
Orders Table (customer_id is FOREIGN KEY)
order_id (PRIMARY KEY)customer_id (FOREIGN KEY)total
101150
102175
1032100
3 rows

A Foreign Key is like a reference or a link between two tables. It connects information in one table to information in another table.

Think of it like:

  • A library card connects you to your borrowed books
  • A receipt connects to your purchased items
  • A class roster connects students to their classes

Real-Life Example: School System

Customers Table
customer_id (PRIMARY KEY)nameemail
1Johnjohn@email.com
2Marymary@email.com
2 rows
Orders Table (customer_id is FOREIGN KEY)
order_id (PRIMARY KEY)customer_id (FOREIGN KEY)total
101150
102175
1032100
3 rows

Students Table:

  • student_id (Primary Key)
  • name
  • age

Classes Table:

  • class_id (Primary Key)
  • student_id (Foreign Key - links to Students table)
  • class_name

The student_id in Classes table is a Foreign Key. It points to the student_id in Students table.

This means: "This class belongs to this student"

Simple Example with Two Tables

Customers Table
customer_id (PRIMARY KEY)nameemail
1Johnjohn@email.com
2Marymary@email.com
2 rows
Orders Table (customer_id is FOREIGN KEY)
order_id (PRIMARY KEY)customer_id (FOREIGN KEY)total
101150
102175
1032100
3 rows

Table 1: Customers (Main Table)

CREATE TABLE customers ( customer_id SERIAL PRIMARY KEY, name VARCHAR(100), email VARCHAR(100) );

Table 2: Orders (Connected Table)

CREATE TABLE orders ( order_id SERIAL PRIMARY KEY, customer_id INTEGER, total DECIMAL(10, 2), FOREIGN KEY (customer_id) REFERENCES customers(customer_id) );

What this means:

  • Each order has a customer_id
  • That customer_id MUST exist in the customers table
  • Cannot create order for non-existent customer

How Foreign Keys Work

Customers Table
customer_id (PRIMARY KEY)nameemail
1Johnjohn@email.com
2Marymary@email.com
2 rows
Orders Table (customer_id is FOREIGN KEY)
order_id (PRIMARY KEY)customer_id (FOREIGN KEY)total
101150
102175
1032100
3 rows

Imagine an online store:

Customers Table:

| customer_id | name | |-------------|-------| | 1 | John | | 2 | Mary |

Orders Table:

| order_id | customer_id | total | |----------|-------------|--------| | 101 | 1 | 50.00 | | 102 | 1 | 75.00 | | 103 | 2 | 100.00 |

The customer_id in Orders links to customer_id in Customers:

  • Order 101 belongs to John (customer 1)
  • Order 102 belongs to John (customer 1)
  • Order 103 belongs to Mary (customer 2)

Why Use Foreign Keys?

Customers Table
customer_id (PRIMARY KEY)nameemail
1Johnjohn@email.com
2Marymary@email.com
2 rows
Orders Table (customer_id is FOREIGN KEY)
order_id (PRIMARY KEY)customer_id (FOREIGN KEY)total
101150
102175
1032100
3 rows

Reason 1: Prevents Bad Data

Without Foreign Key:

-- Can create order for customer that does not exist INSERT INTO orders (customer_id, total) VALUES (999, 50.00); -- Customer 999 does not exist!

With Foreign Key:

-- Cannot create order for non-existent customer INSERT INTO orders (customer_id, total) VALUES (999, 50.00); -- ERROR: Customer 999 not found

Reason 2: Keeps Data Connected

You can easily find:

  • All orders for a customer
  • Which customer placed an order
  • Connected information

Reason 3: Prevents Accidental Deletion

With Foreign Key, you cannot delete a customer who has orders:

DELETE FROM customers WHERE customer_id = 1; -- ERROR: Cannot delete because orders exist for this customer

This prevents losing important connections.

Real-World Examples

Customers Table
customer_id (PRIMARY KEY)nameemail
1Johnjohn@email.com
2Marymary@email.com
2 rows
Orders Table (customer_id is FOREIGN KEY)
order_id (PRIMARY KEY)customer_id (FOREIGN KEY)total
101150
102175
1032100
3 rows

Example 1: Library System

Books Table:

CREATE TABLE books ( book_id SERIAL PRIMARY KEY, title VARCHAR(200), author VARCHAR(100) );

Borrowed Books Table:

CREATE TABLE borrowed_books ( borrow_id SERIAL PRIMARY KEY, book_id INTEGER, borrower_name VARCHAR(100), borrow_date DATE, FOREIGN KEY (book_id) REFERENCES books(book_id) );

The book_id connects borrowed records to actual books.

Example 2: Blog System

Authors Table:

CREATE TABLE authors ( author_id SERIAL PRIMARY KEY, author_name VARCHAR(100), email VARCHAR(100) );

Posts Table:

CREATE TABLE posts ( post_id SERIAL PRIMARY KEY, author_id INTEGER, title VARCHAR(200), content TEXT, FOREIGN KEY (author_id) REFERENCES authors(author_id) );

Each post belongs to an author.

Example 3: School Enrollment

Students Table:

CREATE TABLE students ( student_id SERIAL PRIMARY KEY, name VARCHAR(100) );

Enrollments Table:

CREATE TABLE enrollments ( enrollment_id SERIAL PRIMARY KEY, student_id INTEGER, course_name VARCHAR(100), FOREIGN KEY (student_id) REFERENCES students(student_id) );

Each enrollment belongs to a student.

Simple Rules for Foreign Keys

Customers Table
customer_id (PRIMARY KEY)nameemail
1Johnjohn@email.com
2Marymary@email.com
2 rows
Orders Table (customer_id is FOREIGN KEY)
order_id (PRIMARY KEY)customer_id (FOREIGN KEY)total
101150
102175
1032100
3 rows
  1. Must reference a Primary Key in another table
  2. Value must exist in the referenced table
  3. Can be NULL (optional relationship)
  4. Can have duplicates (many orders for one customer)
  5. Protects data from becoming disconnected

Common Mistakes

Customers Table
customer_id (PRIMARY KEY)nameemail
1Johnjohn@email.com
2Marymary@email.com
2 rows
Orders Table (customer_id is FOREIGN KEY)
order_id (PRIMARY KEY)customer_id (FOREIGN KEY)total
101150
102175
1032100
3 rows

Mistake 1: Referencing Non-Existent Value

Wrong:

-- Customer 999 does not exist INSERT INTO orders (customer_id, total) VALUES (999, 100.00); -- ERROR

Correct:

-- Customer 1 exists INSERT INTO orders (customer_id, total) VALUES (1, 100.00); -- OK

Mistake 2: Deleting Referenced Data

Wrong:

-- Cannot delete customer with orders DELETE FROM customers WHERE customer_id = 1; -- ERROR

Correct approach:

-- First delete orders DELETE FROM orders WHERE customer_id = 1; -- Then delete customer DELETE FROM customers WHERE customer_id = 1; -- OK

Summary

Customers Table
customer_id (PRIMARY KEY)nameemail
1Johnjohn@email.com
2Marymary@email.com
2 rows
Orders Table (customer_id is FOREIGN KEY)
order_id (PRIMARY KEY)customer_id (FOREIGN KEY)total
101150
102175
1032100
3 rows

Think of Foreign Key like:

  • A receipt that points to your credit card
  • A boarding pass that points to your flight
  • A key that points to a specific lock

Key points:

  1. Foreign Key links two tables together
  2. Like a reference or pointer
  3. Must point to existing data
  4. Prevents orphaned data (orders without customers)
  5. Keeps your database organized
  6. Protects data integrity
  7. Makes relationships clear
  8. Easy to find connected information

What Comes Next

Customers Table
customer_id (PRIMARY KEY)nameemail
1Johnjohn@email.com
2Marymary@email.com
2 rows
Orders Table (customer_id is FOREIGN KEY)
order_id (PRIMARY KEY)customer_id (FOREIGN KEY)total
101150
102175
1032100
3 rows

Now you know how tables connect with Foreign Keys. Next, you will learn about UNIQUE Constraint to prevent duplicate values.

Finished this topic?

Mark it complete to track your progress and maintain your streak!

Previous
Aggregate Queries
Next
UNIQUE Constraint
SkillsetMaster - AI, Web Development & Data Analytics Courses