Composite Keys vs Surrogate Keys

Evaluate the trade-offs between using natural business keys and auto-incrementing integers for row identification.

In this lesson, you will learn how to choose between using existing business data or synthetic IDs to uniquely identify your rows.

The Two Approaches

When we define a Primary Key, we have two main strategies.

A Composite Key (often called a Natural Key) uses existing attributes from your business domain—like a combination of Email and Date—to ensure a row is unique. For example, in a library system, you might use BookID and CheckoutDate as a composite key because a specific book can only be checked out once on any given day.

A Surrogate Key is a synthetic, artificial value—usually an auto-incrementing integer—created specifically for the database. The database assigns 1, 2, 3... to every new row, regardless of what the actual data looks like.

Evaluating the Trade-offs

The primary benefit of a Composite Key is that it guarantees data integrity by strictly enforcing the business rules within the table schema. However, if your business logic changes (for example, if the library decides a book can be checked out multiple times a day due to an update in their system), your entire key structure breaks, and you must update every relationship that references it.

A Surrogate Key is highly stable. Because the ID (like an EmployeeID) has no inherent meaning to the business, it never needs to change. If an employee changes their name or address, their EmployeeID remains the same, keeping your relationships intact. The downside is that surrogate keys don't inherently prevent "duplicate" entries; you would need to add a separate UNIQUE constraint to the business columns to prevent two different IDs from representing the exact same real-world entity.

-- Composite Key example
CREATE TABLE Checkouts (
    BookID INT,
    CheckoutDate DATE,
    PRIMARY KEY (BookID, CheckoutDate)
);

-- Surrogate Key example
CREATE TABLE Employees (
    EmployeeID INT AUTO_INCREMENT PRIMARY KEY,
    Email VARCHAR(255) UNIQUE
);

While composite keys feel intuitive, surrogate keys are the standard in modern design because they remain constant even when the world around your data shifts.

Key Takeaway: Surrogate keys provide stability and flexibility, while composite keys enforce business-specific uniqueness constraints at the cost of being harder to change later.