Foreign Keys
Foreign keys define relationships between tables and enforce referential integrity - ensuring that a referenced row actually exists. They are the mechanism that makes JOINs meaningful and prevents orphaned records.
Foreign Key Concepts
- REFERENCES parent_table(column) - defines the relationship
- Prevents inserting order with a non-existent user_id
- Referenced column must be PRIMARY KEY or UNIQUE
- ON DELETE CASCADE - delete child rows when parent deleted
- ON DELETE SET NULL - set FK to NULL when parent deleted
- ON DELETE RESTRICT / NO ACTION - block parent deletion if children exist (default)
Foreign Key Examples
-- orders.user_id references users.id
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id) ON DELETE CASCADE,
total_amount DECIMAL(10,2),
status VARCHAR(50)
);
-- Violation: insert order for non-existent user
INSERT INTO orders (user_id, total_amount, status)
VALUES (999, 100.00, 'pending');
-- ERROR: insert or update on table "orders" violates
-- foreign key constraint "orders_user_id_fkey"
-- Detail: Key (user_id)=(999) is not present in table "users".
-- Add FK to existing table
ALTER TABLE orders
ADD CONSTRAINT fk_order_user
FOREIGN KEY (user_id) REFERENCES users(id);
-- List foreign keys (PostgreSQL)
SELECT conname, contype, confdeltype
FROM pg_constraint
WHERE conrelid = 'orders'::regclass;
-- Check for orphaned rows (rows with broken FK)
SELECT o.id FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;Quick Quiz
Tip
Tip
Practice Foreign Keys in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Constraints enforce data integrity at DB level.
Common Mistake
Warning
A common mistake with Foreign Keys is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Foreign Keys from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- Foreign keys define relationships between tables and enforce referential integrity - ensuring that a referenced row actually exists.
- REFERENCES parent_table(column) - defines the relationship
- Prevents inserting order with a non-existent user_id
- Referenced column must be PRIMARY KEY or UNIQUE