Composite Primary Keys

Objectives

By the end of this lesson, you should be able to:

• Create a primary key made of more than one column
• Explain what uniqueness means for a composite primary key
• Recognize when a composite primary key fits better than a single-column one

💡 Why this matters: Not every table has one natural unique column. “Which product, in which warehouse” only makes sense as a pair, neither warehouse_id nor product_id alone is unique, but the combination is.

⚠️ A note on verification: every statement and result in this lesson was run against a real, live PostgreSQL 18 database.

A Table Without a Single Natural Key

CREATE TABLE warehouse_inventory (
    warehouse_id INTEGER NOT NULL,
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 0,
    PRIMARY KEY (warehouse_id, product_id)
);

INSERT INTO warehouse_inventory (warehouse_id, product_id, quantity) VALUES
  (1, 100, 50),
  (1, 101, 20),
  (2, 100, 15);

PRIMARY KEY (warehouse_id, product_id) declares a composite primary key, the combination of both columns must be unique, not either column on its own. Warehouse 1 stores product 100 (50 units) and product 101 (20 units), warehouse 2 also stores product 100 (15 units), product_id = 100 repeats across rows, and that’s fine, the pairing (1, 100) and (2, 100) are still different combinations.

What Uniqueness Means Here

INSERT INTO warehouse_inventory (warehouse_id, product_id, quantity) VALUES (1, 100, 99);

ERROR:  duplicate key value violates unique constraint "warehouse_inventory_pkey"

Warehouse 1 already has a row for product 100, this exact pairing already exists, so it’s rejected. But a new pairing succeeds even when one column repeats:

INSERT INTO warehouse_inventory (warehouse_id, product_id, quantity) VALUES (3, 100, 5);

Product 100 now appears in three different warehouses, three separate, valid rows, because each pairs with a different warehouse_id.

Every Column in a Composite Key Is NOT NULL

INSERT INTO warehouse_inventory (warehouse_id, product_id, quantity) VALUES (NULL, 105, 10);

ERROR:  null value in column "warehouse_id" of relation "warehouse_inventory" violates not-null constraint

Just like a single-column primary key (Module 3), every column that’s part of a composite primary key is automatically NOT NULL, a NULL in either column would break the “this combination uniquely identifies a row” guarantee a primary key exists to provide.

When to Reach for One

A composite primary key fits naturally when a row is genuinely defined by a combination of values, a junction table (previous lesson, student_id + course_id), or a natural pairing like this lesson’s warehouse and product. A single-column SERIAL primary key (Module 3) fits better when rows don’t have an obvious natural combination, or when other tables need a single simple value to reference as a foreign key. Both are valid choices, the data’s actual shape decides which one fits.

Try It

1. Create a seat_assignments table with flight_id, seat_number, and passenger_name, using a composite primary key on flight_id and seat_number.
2. Insert two seat assignments for the same flight with different seat numbers, and confirm both succeed.
3. Try inserting a duplicate (flight_id, seat_number) pairing and read the resulting error.
4. Explain, in your own words, why seat_number alone couldn’t be the primary key for this table.

Recap

• A composite primary key is made of two or more columns, uniqueness applies to the combination, not any single column.
• Every column in a composite primary key is automatically NOT NULL, same as a single-column primary key.
• Composite primary keys fit naturally for junction tables and other data genuinely defined by a combination of values.

Next lesson: referential integrity, and what happens to related rows when the row they point to changes or disappears.