Course contentsShow
← Data Science
Lesson 1056 of 2,14527. Database Design and OptimizationFree lesson

Foreign Key Constraints in Practice

How to implement foreign key constraints to enforce referential integrity and prevent orphaned records.

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_id in orders must exist in customers.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.