Composite Keys in Databases: Design, Use Cases and Pitfalls

-- Scenario: A university enrolment system.
-- A student can enrol in many courses, a course has many students.
-- The COMBINATION of student_id + course_id must be unique.

CREATE TABLE students (
    student_id   INT          NOT NULL,
    full_name    VARCHAR(120) NOT NULL,
    email        VARCHAR(255) NOT NULL UNIQUE,
    enrolled_on  DATE         NOT NULL,
    PRIMARY KEY (student_id)   -- single-column PK on the parent table
);

CREATE TABLE courses (
    course_id    INT          NOT NULL,
    course_name  VARCHAR(200) NOT NULL,
    credit_hours TINYINT      NOT NULL,
    PRIMARY KEY (course_id)    -- single-column PK on the parent table
);

CREATE TABLE enrolments (
    student_id   INT  NOT NULL,
    course_id    INT  NOT NULL,
    enrolment_date DATE NOT NULL,
    grade        CHAR(2),                         -- nullable until graded

    -- The composite primary key: the PAIR must be unique
    PRIMARY KEY (student_id, course_id),

    -- Foreign keys back to parent tables keep referential integrity intact
    CONSTRAINT fk_enrolment_student
        FOREIGN KEY (student_id) REFERENCES students(student_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_enrolment_course
        FOREIGN KEY (course_id)  REFERENCES courses(course_id)
        ON DELETE RESTRICT
);

-- Seed some data so we can see the constraint in action
INSERT INTO students VALUES (1, 'Maria Reyes',   'maria@uni.edu',  '2024-09-01');
INSERT INTO students VALUES (2, 'David Okafor',  'david@uni.edu',  '2024-09-01');

INSERT INTO courses  VALUES (101, 'Intro to Databases', 3);
INSERT INTO courses  VALUES (102, 'Algorithms I',       4);

-- Valid enrolments: different (student_id, course_id) pairs
INSERT INTO enrolments VALUES (1, 101, '2024-09-05', NULL);
INSERT INTO enrolments VALUES (1, 102, '2024-09-05', NULL);
INSERT INTO enrolments VALUES (2, 101, '2024-09-06', NULL);

-- This will FAIL — same student, same course: duplicate composite key
-- INSERT INTO enrolments VALUES (1, 101, '2024-09-10', NULL);
-- ERROR 1062 (23000): Duplicate entry '1-101' for key 'enrolments.PRIMARY'

SELECT
    s.full_name    AS student,
    c.course_name  AS course,
    e.enrolment_date
FROM enrolments e
JOIN students s ON s.student_id = e.student_id
JOIN courses  c ON c.course_id  = e.course_id
ORDER BY s.full_name, c.course_name;

-- Scenario: E-commerce order system.
-- An order has many line items. Each line item is uniquely identified
-- by the order it belongs to AND the product on that line.

CREATE TABLE orders (
    order_id       INT          NOT NULL,
    customer_email VARCHAR(255) NOT NULL,
    order_date     DATE         NOT NULL,
    PRIMARY KEY (order_id)
);

CREATE TABLE products (
    product_id   INT          NOT NULL,
    product_name VARCHAR(200) NOT NULL,
    unit_price   DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (product_id)
);

-- order_line_items uses a composite PK: one order can't have the
-- same product listed twice (you'd just increase the quantity instead)
CREATE TABLE order_line_items (
    order_id    INT            NOT NULL,
    product_id  INT            NOT NULL,
    quantity    SMALLINT       NOT NULL CHECK (quantity > 0),
    unit_price  DECIMAL(10,2)  NOT NULL,  -- price at time of purchase, may differ from product table

    PRIMARY KEY (order_id, product_id),   -- composite PK

    CONSTRAINT fk_lineitem_order
        FOREIGN KEY (order_id)   REFERENCES orders(order_id)   ON DELETE CASCADE,
    CONSTRAINT fk_lineitem_product
        FOREIGN KEY (product_id) REFERENCES products(product_id) ON DELETE RESTRICT
);

-- A returns table must carry BOTH columns of the parent composite key
CREATE TABLE returns (
    return_id      INT     NOT NULL AUTO_INCREMENT,
    order_id       INT     NOT NULL,       -- \  Both columns needed
    product_id     INT     NOT NULL,       -- /  to reference parent
    return_reason  VARCHAR(500),
    returned_on    DATE    NOT NULL,

    PRIMARY KEY (return_id),

    -- The composite FK references the composite PK of order_line_items
    CONSTRAINT fk_return_lineitem
        FOREIGN KEY (order_id, product_id)
        REFERENCES order_line_items(order_id, product_id)
        ON DELETE CASCADE
);

-- Insert demo data
INSERT INTO orders   VALUES (1001, 'alice@shop.com', '2024-11-01');
INSERT INTO products VALUES (5,   'Mechanical Keyboard', 89.99);
INSERT INTO products VALUES (9,   'USB-C Hub',           34.50);

INSERT INTO order_line_items VALUES (1001, 5, 1, 89.99);
INSERT INTO order_line_items VALUES (1001, 9, 2, 34.50);

INSERT INTO returns VALUES (NULL, 1001, 9, 'Wrong colour delivered', '2024-11-08');

-- Confirm the return links correctly back through the chain
SELECT
    r.return_id,
    p.product_name,
    r.return_reason,
    r.returned_on
FROM returns r
JOIN order_line_items oli ON oli.order_id   = r.order_id
                         AND oli.product_id = r.product_id
JOIN products         p   ON p.product_id   = oli.product_id;

-- Scenario: Airport route pricing.
-- Routes are naturally identified by (origin, destination, airline_code).
-- We add a surrogate PK for compact FK references, but enforce
-- the natural composite uniqueness with a UNIQUE constraint.

CREATE TABLE routes (
    route_id      INT         NOT NULL AUTO_INCREMENT,
    origin        CHAR(3)     NOT NULL,   -- IATA airport code e.g. 'JFK'
    destination   CHAR(3)     NOT NULL,   -- IATA airport code e.g. 'LAX'
    airline_code  CHAR(2)     NOT NULL,   -- IATA airline code e.g. 'AA'
    base_price    DECIMAL(8,2) NOT NULL,

    -- Surrogate PK: compact, stable, easy for child tables to reference
    PRIMARY KEY (route_id),

    -- Natural composite UNIQUE constraint: enforces business rule
    -- that no two rows can represent the same actual route on same airline
    CONSTRAINT uq_route_natural
        UNIQUE (origin, destination, airline_code)
);

-- Child table uses the compact surrogate — much cleaner than
-- carrying three columns forward from the parent
CREATE TABLE flight_schedules (
    schedule_id   INT       NOT NULL AUTO_INCREMENT,
    route_id      INT       NOT NULL,            -- single-column FK, nice and clean
    departure_time TIME     NOT NULL,
    days_of_week  VARCHAR(7) NOT NULL,           -- e.g. 'MTWTFSS'

    PRIMARY KEY (schedule_id),
    CONSTRAINT fk_schedule_route
        FOREIGN KEY (route_id) REFERENCES routes(route_id) ON DELETE CASCADE
);

-- Populate
INSERT INTO routes (origin, destination, airline_code, base_price)
VALUES
    ('JFK', 'LAX', 'AA', 249.00),
    ('JFK', 'LAX', 'DL', 229.00),  -- same airports, different airline: valid
    ('LAX', 'ORD', 'UA', 189.00);

-- This would fail: same natural key already exists
-- INSERT INTO routes (origin, destination, airline_code, base_price)
-- VALUES ('JFK', 'LAX', 'AA', 199.00);
-- ERROR: Duplicate entry 'JFK-LAX-AA' for key 'routes.uq_route_natural'

SELECT route_id, origin, destination, airline_code, base_price
FROM routes
ORDER BY origin, destination, airline_code;

-- Continuing with the enrolments table from Section 1.
-- Demonstrating query patterns that use the composite index well vs. poorly.

-- GOOD: Filters on the leading column (student_id) — uses the index efficiently
-- Retrieves all courses for a specific student
SELECT
    e.course_id,
    c.course_name,
    e.grade
FROM enrolments e
JOIN courses c ON c.course_id = e.course_id
WHERE e.student_id = 1;    -- leading column: index seek
-- Expected output:
-- course_id | course_name          | grade
-- 101       | Intro to Databases   | NULL
-- 102       | Algorithms I         | NULL

-- GOOD: Filters on both columns — pinpoint lookup, fastest possible
SELECT grade
FROM enrolments
WHERE student_id = 1 AND course_id = 101;  -- full composite key: optimal
-- Expected output:
-- grade
-- NULL

-- POTENTIALLY SLOW: Filters only on the trailing column (course_id)
-- The composite index (student_id, course_id) cannot be used efficiently here
SELECT student_id
FROM enrolments
WHERE course_id = 101;   -- trailing column only: full scan on composite index
-- Fix: Add a separate secondary index
CREATE INDEX idx_enrolments_course
    ON enrolments (course_id);   -- now this query gets its own index to use

-- Verify with EXPLAIN that the index is being picked up
EXPLAIN SELECT student_id
FROM enrolments
WHERE course_id = 101;