Primary Key and Foreign Key Explained — Database Design for Beginners

-- Create a customers table with a proper primary key
-- AUTO_INCREMENT means the database assigns the next number automatically
-- You never insert a value for customer_id yourself
CREATE TABLE customers (
    customer_id   INT           NOT NULL AUTO_INCREMENT,  -- the primary key column
    full_name     VARCHAR(100)  NOT NULL,                 -- customer's full name
    email_address VARCHAR(150)  NOT NULL,                 -- must be provided
    join_date     DATE          NOT NULL,                 -- when they signed up

    -- This line officially declares customer_id as the primary key
    PRIMARY KEY (customer_id)
);

-- Insert three customers — notice we do NOT provide customer_id
-- The database fills it in for us: 1, 2, 3
INSERT INTO customers (full_name, email_address, join_date)
VALUES
    ('Alice Mercer',  'alice@example.com',  '2023-01-15'),
    ('James Brown',   'jbrown@example.com', '2023-03-22'),
    ('James Brown',   'jb2@example.com',    '2024-07-01');  -- same name, different person

-- Retrieve all customers to see the auto-assigned IDs
SELECT customer_id, full_name, email_address FROM customers;

-- Try inserting a duplicate primary key to see the protection in action
-- This will FAIL — the database will reject it
INSERT INTO customers (customer_id, full_name, email_address, join_date)
VALUES (1, 'Hacker Person', 'hack@example.com', '2024-01-01');
-- ERROR: Duplicate entry '1' for key 'PRIMARY'

-- We already have a customers table with customer_id as the primary key.
-- Now we create an orders table that links to it via a foreign key.

CREATE TABLE orders (
    order_id      INT            NOT NULL AUTO_INCREMENT,  -- primary key for this table
    customer_id   INT            NOT NULL,                  -- foreign key: references customers
    order_date    DATE           NOT NULL,
    total_amount  DECIMAL(10,2)  NOT NULL,
    order_status  VARCHAR(20)    NOT NULL DEFAULT 'pending',

    -- Declare order_id as this table's own primary key
    PRIMARY KEY (order_id),

    -- Declare the foreign key relationship
    -- customer_id in THIS table must match a customer_id in the customers table
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers (customer_id)
        ON DELETE RESTRICT   -- prevent deleting a customer who has orders
        ON UPDATE CASCADE    -- if customer_id changes, update it here too
);

-- Insert a valid order for Alice (customer_id = 1) — this works
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (1, '2024-02-10', 59.99);

-- Insert another valid order for James Brown (customer_id = 2) — this works
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (2, '2024-03-05', 120.00);

-- Try to insert an order for customer_id = 999 — does not exist in customers
-- The foreign key constraint will BLOCK this
INSERT INTO orders (customer_id, order_date, total_amount)
VALUES (999, '2024-04-01', 45.00);
-- ERROR: Cannot add or update a child row: foreign key constraint fails

-- Join the two tables to see orders with the customer's name
-- This is the power of the foreign key — we can reunite the data
SELECT
    o.order_id,
    c.full_name       AS customer_name,
    o.order_date,
    o.total_amount,
    o.order_status
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;  -- follow the foreign key bridge

-- Example 1: Composite Primary Key
-- The enrolments table tracks student-course pairings
-- A student cannot enrol in the same course twice
CREATE TABLE enrolments (
    student_id  INT  NOT NULL,   -- foreign key pointing to students table
    course_id   INT  NOT NULL,   -- foreign key pointing to courses table
    enrol_date  DATE NOT NULL,
    grade       CHAR(2),         -- NULL until the course is completed

    -- The PRIMARY KEY is the COMBINATION of both columns
    -- Neither column alone is unique; the pair is
    PRIMARY KEY (student_id, course_id),

    CONSTRAINT fk_enrolment_student
        FOREIGN KEY (student_id) REFERENCES students (student_id),

    CONSTRAINT fk_enrolment_course
        FOREIGN KEY (course_id) REFERENCES courses (course_id)
);

-- This inserts fine — student 1 enrolling in course 10
INSERT INTO enrolments (student_id, course_id, enrol_date)
VALUES (1, 10, '2024-09-01');

-- This also inserts fine — student 1 enrolling in a DIFFERENT course
INSERT INTO enrolments (student_id, course_id, enrol_date)
VALUES (1, 11, '2024-09-01');

-- This FAILS — student 1 is already in course 10 (duplicate composite key)
INSERT INTO enrolments (student_id, course_id, enrol_date)
VALUES (1, 10, '2024-10-01');
-- ERROR: Duplicate entry '1-10' for key 'PRIMARY'

-- -------------------------------------------------------
-- Example 2: Surrogate key + UNIQUE constraint on email
-- The surrogate key (customer_id) is the real primary key
-- The UNIQUE constraint stops duplicate emails without making email the PK
CREATE TABLE members (
    member_id     INT          NOT NULL AUTO_INCREMENT,
    email_address VARCHAR(150) NOT NULL,
    full_name     VARCHAR(100) NOT NULL,

    PRIMARY KEY (member_id),

    -- Enforce email uniqueness separately — best of both worlds
    CONSTRAINT uq_members_email UNIQUE (email_address)
);

-- Valid insert
INSERT INTO members (email_address, full_name)
VALUES ('carol@example.com', 'Carol Danes');

-- This fails — email already exists
INSERT INTO members (email_address, full_name)
VALUES ('carol@example.com', 'Carol Smith');
-- ERROR: Duplicate entry 'carol@example.com' for key 'uq_members_email'