Primary Key & Foreign Key — Index Took Down Checkout

Every app you've ever used — Instagram, Spotify, your banking app — stores its data in a database. Users, posts, songs, transactions: all of it lives in tables. But here's the thing: data almost never exists in isolation. A post belongs to a user. A transaction belongs to an account. An order belongs to a customer. The entire system only works because there's a mechanism that links these tables together reliably and without chaos. That mechanism starts with primary keys and foreign keys.

Without them, you'd face a nightmare. Imagine storing a customer's name, address, and phone number on every single order they ever placed. When they move house, you'd have to update hundreds of rows. Miss one? Now your database is lying to you. Or imagine deleting a customer record but leaving all their orphaned orders sitting in the database pointing to nobody. Primary keys and foreign keys exist specifically to prevent these disasters — they enforce identity and relationships at the database level, so your data stays truthful.

By the end of this article you'll understand what a primary key is and why every table needs one, what a foreign key is and how it stitches tables together, how to write the SQL to create both from scratch, the classic mistakes beginners make and exactly how to fix them, and the interview questions that trip people up. You'll walk away ready to design a real, properly connected database.

What Is a Primary Key — And Why Every Table Needs One

A primary key is a column (or combination of columns) in a database table whose value uniquely identifies every single row. Think of it like a passport number. Two people can share the same name, birthday, even the same hometown — but no two people share a passport number. That uniqueness is exactly what a primary key guarantees.

Why does this matter so much? Because databases need a reliable way to say 'I mean THIS exact row, not any other.' When you want to update a customer's email address, you can't search by name — what if two customers are both called 'James Brown'? You need one column that is guaranteed to be different for every row. That's your primary key.

A valid primary key follows three rules: it must be unique across all rows, it must never be NULL (empty), and it should never change once assigned. That last rule is important. If you use someone's email address as a primary key and they change their email, every reference to that key across your whole database breaks. This is why databases typically use a simple auto-incrementing integer — like 1, 2, 3, 4 — as the primary key. It's meaningless outside the database, which is exactly what you want: stable, dumb, permanent.

-- 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'

What Is a Foreign Key — The Bridge Between Tables

Once every table has its own primary key, you need a way to say 'this row in Table A belongs to that row in Table B.' That's exactly what a foreign key does. A foreign key is a column in one table that stores the primary key value from another table. It's the bridge.

Back to the library analogy: when a student borrows a book, the checkout record doesn't copy out the student's full name, address, grade, and teacher. It just stores the student's ID number. That ID number is a foreign key — it points back to the full student record. To get the student's name, you follow the pointer.

This design is called normalization, and it's a fundamental principle of good database design. Store each piece of information in exactly one place. When Alice Mercer moves house, you update her address in one row of the customers table. Every order, every message, every record that references her customer_id automatically reflects the new address. No hunting, no inconsistency.

The foreign key also acts as a guardian. By default, a database with a foreign key constraint will refuse to let you insert an order for a customer_id that doesn't exist in the customers table. It also prevents you from deleting a customer who still has active orders. This is called referential integrity — the database enforces that your references always point to something real.

-- 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

Composite Keys, Natural Keys, and When to Break the Rules

So far we've used a single auto-incrementing integer as the primary key, and that covers 90% of real-world cases. But you'll occasionally encounter two variations worth knowing about.

A composite primary key uses two or more columns together to form the unique identifier. Imagine a table tracking which students are enrolled in which courses. A student can enrol in many courses, and a course can have many students — but a specific student can only be enrolled in a specific course once. So the combination of (student_id, course_id) is what must be unique. Neither column alone is the primary key; together they are.

A natural key uses a real-world piece of data that happens to be unique — like a national insurance number, ISBN, or vehicle registration plate. Some teams prefer these because they carry meaning. The risk, as mentioned earlier, is that real-world data can change or have edge cases (two editions of a book with the same ISBN is a real problem). A surrogate key — the auto-incremented integer — has no meaning outside the database and never changes, which is why most modern systems prefer it.

The rule of thumb: use a surrogate key by default. Add a UNIQUE constraint on natural data (like email addresses) if you need to enforce uniqueness there too. That way you get the stability of a surrogate key AND the uniqueness guarantee on the real-world field.

-- 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'

ON DELETE and ON UPDATE: Choosing the Right Referential Action

When you create a foreign key, you have to decide what happens when the parent row is deleted or its primary key is updated. The database offers several options, and each has real consequences.

• RESTRICT: prevents the operation entirely if any child rows exist. This is the safest default for financial or critical data — it forces you to handle children explicitly.
• CASCADE: automatically deletes or updates all child rows. Useful when children are owned by the parent (e.g., user and their messages) but dangerous if misapplied to data that shouldn't be silently removed.
• SET NULL: sets the foreign key column in child rows to NULL. Useful for optional relationships (e.g., an order with no assigned salesperson), but can leave orphaned rows.
• SET DEFAULT: sets the foreign key column to a predefined default value. Rarely used in practice because of the tight coupling.
• NO ACTION: similar to RESTRICT in most databases, but the check is deferred until commit in some systems (like PostgreSQL with BEGIN...COMMIT).

The choice depends entirely on your data semantics. For example, deleting a customer who has orders should be blocked (RESTRICT) or you should archive instead. Deleting a blog post should also delete its comments (CASCADE). There's no universal right answer.

-- Example 1: ON DELETE CASCADE — when child rows should be removed with the parent
CREATE TABLE user_messages (
    message_id INT AUTO_INCREMENT,
    user_id    INT NOT NULL,
    content    TEXT,
    PRIMARY KEY (message_id),
    CONSTRAINT fk_user_messages
        FOREIGN KEY (user_id)
        REFERENCES users (user_id)
        ON DELETE CASCADE
);

-- Deleting a user will automatically delete all their messages
DELETE FROM users WHERE user_id = 42;

-- Example 2: ON DELETE SET NULL — for optional relationships
CREATE TABLE orders (
    order_id      INT AUTO_INCREMENT,
    customer_id   INT,                    -- nullable, because salesperson can be unassigned
    salesperson_id INT,                   -- foreign key to employees table
    PRIMARY KEY (order_id),
    CONSTRAINT fk_orders_salesperson
        FOREIGN KEY (salesperson_id)
        REFERENCES employees (employee_id)
        ON DELETE SET NULL
);

-- Deleting an employee will set salesperson_id to NULL in affected orders
DELETE FROM employees WHERE employee_id = 7;

-- Example 3: ON UPDATE CASCADE — ensures child rows follow the parent's new PK
CREATE TABLE order_items (
    order_id   INT NOT NULL,
    product_id INT NOT NULL,
    quantity   INT,
    PRIMARY KEY (order_id, product_id),
    CONSTRAINT fk_order_items_order
        FOREIGN KEY (order_id)
        REFERENCES orders (order_id)
        ON UPDATE CASCADE
);

-- If the order_id changes in the parent, it will be updated here too

Performance Considerations: Indexing Foreign Keys and Avoiding Common Pitfalls

A foreign key column without an index is the silent killer of JOIN performance. When you run a query that joins on the foreign key, the database engine must scan the entire child table to find matching rows if there's no index. As the table grows, this full table scan becomes a bottleneck.

But there's more: foreign key constraints themselves add overhead. On every INSERT into the child table, the database must verify that the foreign key value exists in the parent table. That's a lookup. If the foreign key is indexed, this lookup is fast (often an index unique scan). Without an index, it triggers a scan on the parent's primary key index (already indexed) but if the foreign key itself is not indexed, the validation step on the child side for certain operations like DELETE on the parent may require checking the child table for referencing rows — and that's where the missing index hurts.

The rule: always create an index on every foreign key column immediately after creating the constraint. Most databases like PostgreSQL and SQL Server do NOT auto-index foreign keys. MySQL/InnoDB does, but MySQL is the exception.

However, indexes come with write overhead. Every INSERT, UPDATE, or DELETE on the table must update all indexes. For high-write tables, batch operations can reduce the overhead. Use EXPLAIN to verify index usage in your queries.

-- Before index: slow JOIN due to full table scan
EXPLAIN SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- Output shows 'Type: ALL' on orders table — full table scan

-- Create the missing index
CREATE INDEX idx_orders_customer_id ON orders (customer_id);

-- After index: index lookup is used
EXPLAIN SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.customer_id;
-- Output shows 'Type: ref' or 'Index lookup' — fast

-- Verify index exists
SHOW INDEX FROM orders;