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;

Primary Key vs. Foreign Key — The Two Laws of Relational Gravity

Stop thinking of them as features. Think of them as the laws that prevent your database from collapsing into a dumpster fire.

A primary key guarantees row-level uniqueness inside a single table. No nulls. No duplicates. Every row gets a single source of truth. Without one, your table is just a bag of records — unidentifiable, untrustworthy, and impossible to join without guesswork.

A foreign key is the contract between tables. It says "this column must exist over there before it can exist here." It's not a hint — it's a constraint enforced at the database level. The moment you rely on application code alone to maintain referential integrity, you've introduced a bug farm.

Here's the hard truth: primary keys are about identity. Foreign keys are about relationships. Mix them up and you'll end up with orphaned rows, broken joins, and debugging sessions that last until 3 AM.

Primary keys are internal. Foreign keys are external. One says "I am this row." The other says "I belong to that row." Respect the difference.

// io.thecodeforge — database tutorial

CREATE TABLE customers (
    customer_id   SERIAL PRIMARY KEY,  -- unique, not null, one per row
    full_name     TEXT NOT NULL
);

CREATE TABLE orders (
    order_id      SERIAL PRIMARY KEY,
    customer_id   INT NOT NULL,
    order_date    DATE DEFAULT CURRENT_DATE,
    -- foreign key enforces existence in customers
    CONSTRAINT fk_orders_customers
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
);

-- This fails: customer 999 doesn't exist
INSERT INTO orders (customer_id, order_date)
VALUES (999, '2024-03-15');

-- ERROR: insert or update on table "orders" violates foreign key constraint "fk_orders_customers"

Designing Relationships — One-to-Many vs. Many-to-Many

You can't throw foreign keys at tables and pray. You need to understand cardinality first, or your schema will look like spaghetti code.

One-to-many is the default relationship. One customer has many orders. You put the foreign key on the "many" side (orders table). That's it. Clean, fast, indexed.

Many-to-many is where rookies get wrecked. Students and courses. Orders and products. You can't jam a foreign key into either table because each row would need multiple IDs. Solution: a junction table. This is not optional. It's not academic. It's the only way to maintain referential integrity without breaking first normal form.

Self-referencing foreign keys are the dark corner. Employee table where manager_id points to employee_id. Works fine — but you must allow NULL for the root record. One mistake: cascade delete blows up your entire org tree. Always use ON DELETE SET NULL or write a recursive cleanup script.

Composite foreign keys? They exist, but avoid them unless you're dealing with legacy schemas or junction tables with natural keys. They eat index space, complicate migrations, and make your JOINs ugly.

Pick the right cardinality upfront. Changing it later means data migration, broken foreign keys, and a ticket that nobody wants to take.

// io.thecodeforge — database tutorial

-- One-to-many: one student, many enrollments
CREATE TABLE enrollments (
    enrollment_id   SERIAL PRIMARY KEY,
    student_id      INT NOT NULL REFERENCES students(student_id),
    course_id       INT NOT NULL REFERENCES courses(course_id),
    enrolled_at     TIMESTAMPTZ DEFAULT NOW(),
    UNIQUE (student_id, course_id)  -- prevents duplicate enrollments
);

-- Self-referencing: employee hierarchy
CREATE TABLE employees (
    employee_id   SERIAL PRIMARY KEY,
    manager_id    INT REFERENCES employees(employee_id),
    full_name     TEXT NOT NULL
);

-- This fails: no row with id 0
INSERT INTO employees (employee_id, manager_id, full_name)
VALUES (1, 0, 'CEO');

Managing Constraints and Naming Conventions

Databases enforce referential integrity through constraints, but poorly named constraints become a maintenance nightmare. Every primary key, foreign key, and index constraint should have a clear name that reveals its purpose at a glance. A foreign key constraint named FK_Order_Customer instantly tells you it links the Order table to Customer. Without this, debugging deadlocks or schema changes wastes hours scanning system tables. Naming conventions also prevent accidental constraint drops: a generic name like FK__Orders__CustId__1234 offers no clue. Adopt a standard: FK_ChildTable_ParentTable for foreign keys, PK_TableName for primaries. This habit pays off during migrations, audits, and when ON DELETE CASCADE accidentally removes data you meant to keep. Constraints are not just rules—they are documentation. Name them accordingly.

// io.thecodeforge — database tutorial

CREATE TABLE Customer (
    CustomerId INT NOT NULL,
    Name NVARCHAR(100) NOT NULL,
    CONSTRAINT PK_Customer PRIMARY KEY (CustomerId)
);

CREATE TABLE [Order] (
    OrderId INT NOT NULL,
    CustomerId INT NOT NULL,
    CONSTRAINT PK_Order PRIMARY KEY (OrderId),
    CONSTRAINT FK_Order_Customer FOREIGN KEY (CustomerId)
        REFERENCES Customer(CustomerId)
        ON DELETE CASCADE
);

-- Bad practice (never default name):
CREATE TABLE OrderItem (
    OrderId INT NOT NULL
    -- SQL Server would auto-name FK__OrderItem__OrderId__XYZ
);

Managing Modifications and Dropping Constraints

Modifying or dropping primary and foreign key constraints in production risks data corruption or cascading failures. A foreign key drop might allow orphaned rows; a primary key drop disables indexing and breaks joins. Always script changes as transactions, and verify dependencies first. To drop a foreign key, use ALTER TABLE ChildTable DROP CONSTRAINT FK_Name. To drop a primary key, you must first drop all foreign keys referencing it. For modifications, never drop and recreate without WITH CHECK or the optimizer ignores the constraint. For large tables, adding a foreign key with NOCHECK then validating inline prevents table locks. The golden rule: test in staging, script every DROP and ADD, and wrap in explicit transactions. A single uncommitted ALTER can block an entire production pipeline.

// io.thecodeforge — database tutorial

BEGIN TRANSACTION;

-- Find all foreign keys referencing Customer
SELECT name FROM sys.foreign_keys
WHERE referenced_object_id = OBJECT_ID('Customer');

-- Drop FK before PK
ALTER TABLE [Order] DROP CONSTRAINT FK_Order_Customer;

-- Now safe to drop PK (rare, but needed for migration)
ALTER TABLE Customer DROP CONSTRAINT PK_Customer;

-- Recreate PK with new structure
ALTER TABLE Customer ADD CONSTRAINT PK_Customer PRIMARY KEY (CustomerId);

-- Re-add FK with validation
ALTER TABLE [Order] ADD CONSTRAINT FK_Order_Customer
FOREIGN KEY (CustomerId) REFERENCES Customer(CustomerId)
WITH CHECK;

COMMIT;