What is a Foreign Key?
A Foreign Key connects two tables. It points to a Primary Key in another table.
Simple rule: Foreign Key = "This belongs to that"
How Foreign Key Works
Basic Syntax
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER,
total DECIMAL(10,2),
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);Example 1: Create Connected Tables
-- Main table (has Primary Key)
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100)
);
-- Connected table (has Foreign Key)
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(customer_id),
total DECIMAL(10,2)
);Example 2: Insert Connected Data
-- First add customer
INSERT INTO customers (name) VALUES ('John');
-- Then add order for that customer
INSERT INTO orders (customer_id, total) VALUES (1, 99.99);Example 3: Query Connected Data
SELECT * FROM orders WHERE customer_id = 1;Foreign Key Rules
- Must reference Primary Key - Points to PK in another table
- Value must exist - Can't reference non-existent customer
- Prevents bad data - Database rejects invalid references
- Can be NULL - Optional relationship allowed
Try It Below
Use the playground to practice:
SELECT * FROM orders WHERE customer_id = 1;SELECT * FROM customers;
What Comes Next
Next: Learn UNIQUE Constraint to prevent duplicate values.
Try Foreign Key
Find orders by customer. Try: SELECT * FROM orders WHERE customer_id = 1;
Source Table: students
| order_id | customer_id | total |
|---|---|---|
| 101 | 1 | 50 |
| 102 | 1 | 75 |
| 103 | 2 | 100 |
3 rows
SQL Editor
Loading...
View Customers
See all customers. The customer_id is the Primary Key.
Source Table: students
| customer_id | name | |
|---|---|---|
| 1 | John | john@email.com |
| 2 | Mary | mary@email.com |
2 rows
SQL Editor
Loading...
Count Orders per Customer
How many orders each customer has?
Source Table: students
| order_id | customer_id | total |
|---|---|---|
| 101 | 1 | 50 |
| 102 | 1 | 75 |
| 103 | 2 | 100 |
3 rows
SQL Editor
Loading...