FOREIGN KEY
A FOREIGN KEY enforces referential integrity - ensuring every value in a column actually exists as a primary key in another table. It prevents orphaned records and defines the relationships that make JOIN queries meaningful.
FOREIGN KEY Rules
- Ensures referenced value exists in the parent table's PRIMARY KEY or UNIQUE column
- Prevents inserting orders with non-existent user IDs
- Can be NULL (represents optional relationship)
- ON DELETE / ON UPDATE actions control cascade behavior
- Both tables must use the same data type for the FK and PK columns
FOREIGN KEY Examples
-- Inline FK definition
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id) ON DELETE CASCADE,
total_amount DECIMAL(10,2) NOT NULL,
status VARCHAR(50) DEFAULT 'pending'
);
-- Explicit FK constraint with a name
CREATE TABLE payments (
id SERIAL PRIMARY KEY,
order_id INT,
amount DECIMAL(10,2),
CONSTRAINT fk_payment_order
FOREIGN KEY (order_id) REFERENCES orders(id)
ON DELETE CASCADE
);
-- Violation demonstration
INSERT INTO orders (user_id, total_amount, status)
VALUES (9999, 100.00, 'pending');
-- ERROR: Key (user_id)=(9999) is not present in table "users"
-- List all FKs on a table (PostgreSQL)
SELECT tc.constraint_name, kcu.column_name, ccu.table_name AS ref_table
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu USING (constraint_name)
JOIN information_schema.constraint_column_usage AS ccu USING (constraint_name)
WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name = 'orders';
-- Drop a FK constraint
ALTER TABLE orders DROP CONSTRAINT fk_payment_order;Quick Quiz
Tip
Tip
Practice FOREIGN KEY 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 KEY 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 KEY 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
- A FOREIGN KEY enforces referential integrity - ensuring every value in a column actually exists as a primary key in another table.
- Ensures referenced value exists in the parent table's PRIMARY KEY or UNIQUE column
- Prevents inserting orders with non-existent user IDs
- Can be NULL (represents optional relationship)