Foreign Keys - Referential Integrity
Introduction
Tables rarely exist in isolation. Customers place orders, orders contain products, users write comments. Foreign keys enforce these relationships, ensuring data integrity.
A foreign key is a column (or columns) that references the primary key of another table, creating a parent-child relationship.
Basic Foreign Key Syntax
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id), -- Foreign key
total DECIMAL(10,2) NOT NULL
);
customer_id must exist in customers.id (or be NULL).
Foreign Key Relationships
One-to-Many
One customer can have many orders:
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id),
total DECIMAL(10,2) NOT NULL
);
One-to-One
One user has one profile:
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE user_profiles (
user_id INTEGER PRIMARY KEY REFERENCES users(id), -- One-to-one
bio TEXT,
avatar_url VARCHAR(255)
);
user_id is both PRIMARY KEY and FOREIGN KEY!
Many-to-Many (Junction Table)
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE courses (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE enrollments (
student_id INTEGER REFERENCES students(id),
course_id INTEGER REFERENCES courses(id),
enrolled_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (student_id, course_id)
);
Referential Actions
Control what happens when referenced row is updated/deleted.
ON DELETE Actions
CASCADE
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE CASCADE
);
Deleting customer → Deletes all their orders automatically
RESTRICT (Default)
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE RESTRICT
);
Cannot delete customer if they have orders.
SET NULL
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL
);
Deleting customer → Sets customer_id to NULL in orders
SET DEFAULT
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id) ON DELETE SET DEFAULT DEFAULT 0
);
Deleting customer → Sets customer_id to default value
NO ACTION
Similar to RESTRICT, but check can be deferred (advanced).
ON UPDATE Actions
Same options as ON DELETE:
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id)
ON UPDATE CASCADE -- Update orders when customer ID changes
ON DELETE CASCADE -- Delete orders when customer is deleted
);
Choosing Referential Actions
Scenario 1: Orders and Customers
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id) ON DELETE RESTRICT
);
RESTRICT: Prevent accidental customer deletion
Scenario 2: Comments and Posts
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE
);
CASCADE: Deleting post should delete all comments
Scenario 3: Orders and Sales Reps
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
sales_rep_id INTEGER REFERENCES employees(id) ON DELETE SET NULL
);
SET NULL: Keep orders even if sales rep leaves
Foreign Key to Different Column
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL
);
CREATE TABLE sessions (
id SERIAL PRIMARY KEY,
user_email VARCHAR(255) REFERENCES users(email) -- Reference email, not id
);
Note: Referenced column must be UNIQUE or PRIMARY KEY!
Composite Foreign Keys
CREATE TABLE order_items (
order_id INTEGER,
product_id INTEGER,
quantity INTEGER,
PRIMARY KEY (order_id, product_id)
);
CREATE TABLE order_shipments (
id SERIAL PRIMARY KEY,
order_id INTEGER,
product_id INTEGER,
tracking_number VARCHAR(100),
FOREIGN KEY (order_id, product_id) REFERENCES order_items(order_id, product_id)
);
Self-Referencing Foreign Keys
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
manager_id INTEGER REFERENCES employees(id) -- Self-reference
);
INSERT INTO employees (id, name, manager_id) VALUES
(1, 'Alice (CEO)', NULL),
(2, 'Bob', 1), -- Bob reports to Alice
(3, 'Carol', 1); -- Carol reports to Alice
Adding Foreign Keys to Existing Tables
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE CASCADE;
Dropping Foreign Keys
ALTER TABLE orders
DROP CONSTRAINT fk_customer;
Checking Foreign Key Constraints
Find All Foreign Keys
SELECT
tc.table_name,
kcu.column_name,
ccu.table_name AS foreign_table,
ccu.column_name AS foreign_column
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu
ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY'
AND tc.table_name = 'orders';
Foreign Key Violations
-- ❌ Error: customer_id 999 doesn't exist
INSERT INTO orders (customer_id, total) VALUES (999, 100.00);
-- ✅ OK: customer_id 1 exists
INSERT INTO orders (customer_id, total) VALUES (1, 100.00);
-- ❌ Error: Cannot delete customer with orders (RESTRICT)
DELETE FROM customers WHERE id = 1;
Best Practices
1. Always Add Indexes on Foreign Keys
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
Why: Speeds up JOINs and referential integrity checks
2. Name Foreign Key Constraints
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER,
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE CASCADE
);
3. Use NOT NULL When Appropriate
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id) -- Every order must have a customer
);
4. Choose Appropriate ON DELETE Action
- CASCADE: Child data is meaningless without parent
- RESTRICT/NO ACTION: Protect against accidental deletion
- SET NULL: Child can exist independently
Practice Exercises
Exercise 1
Create authors and books tables with a one-to-many relationship.
Solution
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE books (
id SERIAL PRIMARY KEY,
title VARCHAR(200) NOT NULL,
author_id INTEGER NOT NULL REFERENCES authors(id) ON DELETE RESTRICT
);
CREATE INDEX idx_books_author_id ON books(author_id);
Exercise 2
Create a many-to-many relationship between students and courses.
Solution
CREATE TABLE students (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE courses (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL
);
CREATE TABLE enrollments (
student_id INTEGER REFERENCES students(id) ON DELETE CASCADE,
course_id INTEGER REFERENCES courses(id) ON DELETE CASCADE,
enrolled_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (student_id, course_id)
);
CREATE INDEX idx_enrollments_student ON enrollments(student_id);
CREATE INDEX idx_enrollments_course ON enrollments(course_id);
Key Takeaways
- ✅ Foreign keys enforce referential integrity
- ✅ ON DELETE CASCADE deletes child rows automatically
- ✅ ON DELETE RESTRICT prevents parent deletion
- ✅ ON DELETE SET NULL keeps child rows but removes reference
- ✅ Index foreign key columns for performance
- ✅ Name constraints for clarity
- ✅ One-to-many, one-to-one, many-to-many relationships
Next Steps
You've mastered foreign keys! In the next lesson, we'll explore Indexes—the secret to fast queries and high-performance databases.
Let's optimize!
اختبار قصير
التالي · الدرس 35 من 44
Indexes - Making Queries Faster
قراءة 6 دقيقة
أنهيت 34 من 44 درسًا

اسأل Byte عن هذا الدرس
محادثة خاصة حول هذا الدرس. قرأ Byte الدرس كاملًا، ويجيب خلال ثوانٍ، ويُظهر القسم الذي تأتي منه كل إجابة. يمكنك السؤال بأي لغة.
الأسئلة والأجوبة
أسئلة عامة حول هذا الدرس. يجيب AI Tutor فورًا بلغتك، ويراجع فريق FreeAcademy الإجابات، ويمكن لكل المتعلمين قراءتها.

