Foreign Key Constraints in Practice
You'll learn how to implement foreign key constraints in real databases to maintain data integrity and prevent orphaned records.
Now that you understand what referential integrity is, let's see how foreign key constraints actually work in practice to enforce it automatically.
The Practical Problem
Without foreign key constraints, nothing stops you from inserting an order record with customer_id = 999 when customer 999 doesn't exist. That creates an "orphaned record"—data pointing to nothing. Foreign keys prevent this at the database level.
Implementing Foreign Keys
When you create a table, you define which columns reference other tables:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT NOT NULL,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
This constraint means:
- Every
customer_idinordersmust exist incustomers.customer_id - You cannot insert an order with a non-existent customer
- You cannot delete a customer who has orders (unless you use cascading actions)
What Happens in Practice
Attempted invalid insert:
INSERT INTO orders (order_id, customer_id, order_date)
VALUES (101, 9999, '2024-01-15');
-- ERROR: violates foreign key constraint
-- customer 9999 does not exist
Attempted problematic delete:
DELETE FROM customers WHERE customer_id = 42;
-- ERROR: violates foreign key constraint
-- orders still reference this customer
The database blocks these operations automatically—you don't need to write validation code in your application.
Adding Constraints to Existing Tables
ALTER TABLE orders
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id) REFERENCES customers(customer_id);
Key Takeaway: Foreign key constraints enforce referential integrity at the database level, automatically preventing orphaned records and ensuring every relationship points to valid data—making your data structure self-protecting.