Relational Integrity Constraints

In this lesson, you will learn how to use keys to enforce business rules and keep your data reliable as it grows.

Ensuring Data Harmony

Up until now, we have defined what data looks like (entities) and how to identify individual rows (primary keys). However, a database is rarely just one table. When you have multiple tables—like a Customers table and an Orders table—you need a way to ensure that these tables "talk" to each other correctly.

This is where Relational Integrity Constraints come in. They act as the "rules of the road" for your database, preventing messy, orphaned, or illogical data from ever being saved.

The Power of Foreign Keys

The primary tool for maintaining this integrity is the Foreign Key. A Foreign Key is a column in one table that points directly to the Primary Key of another table.

Imagine you have these two tables:

-- The 'parent' table
CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    name TEXT
);

-- The 'child' table
CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

By adding that FOREIGN KEY constraint, you are telling the database: "An order cannot exist for a customer who doesn't exist in our system."

Why This Matters

Without these constraints, your database could easily become inconsistent. For example, without a Foreign Key, you could accidentally record an order for "Customer ID 999," even if that customer was never registered.

Relational Integrity Constraints ensure two critical things:

1. Consistency: Every child record (like an order) must link to a valid parent record (the customer).
2. Protection: The database will reject any operation that breaks these links, effectively enforcing your business logic automatically. By defining these relationships once, you stop worrying about "broken" data before it ever hits your storage.

Key Takeaway: Relational integrity constraints use Foreign Keys to create unbreakable links between tables, ensuring that your data remains accurate and logically consistent across your entire database.