Primary and Foreign Keys

Learning Focus

Use this lesson to understand primary keys and foreign keys — the two fundamental tools that define identity and enforce relationships between tables.

Concept Overview

What Are Keys?

In a relational database, keys are columns (or combinations of columns) that uniquely identify rows and establish connections between tables.

• A primary key uniquely identifies every row in a table. No two rows can share the same primary key value, and it can never be NULL.
• A foreign key is a column in one table that references the primary key of another table. It enforces referential integrity — meaning you cannot insert a child row that points to a parent that doesn't exist.

Why Are Keys Important?

Without keys, your database is just a collection of unrelated spreadsheets. Keys give you:

• Data integrity — orphan rows (orders pointing to deleted customers) become impossible
• Query reliability — JOINs between tables produce correct, predictable results
• Self-documenting schema — anyone reading the schema can understand table relationships

Where Keys Fit in Database Design

Keys are the foundation of relational modeling. Every schema design starts by asking:

1. What uniquely identifies a row? → Primary key
2. How do tables relate to each other? → Foreign keys

---

Basic Syntax & Rules

Primary Key Syntax

You can define a primary key inline or as a table-level constraint:

-- Inline (single column)
CREATE TABLE customers (
    customer_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    full_name   VARCHAR(120) NOT NULL
);

-- Table-level constraint (useful for composite keys)
CREATE TABLE order_items (
    order_id   BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity   INT NOT NULL DEFAULT 1,
    PRIMARY KEY (order_id, product_id)
);

Foreign Key Syntax

Foreign keys reference the primary key (or unique key) of another table:

CREATE TABLE orders (
    order_id     BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id  BIGINT NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    order_date   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

Key Parameters

Element	Description
AUTO_INCREMENT	MySQL generates the next integer automatically
CONSTRAINT fk_name	Names the foreign key for easier debugging and migration
REFERENCES parent(column)	Points to the parent table and column
ON UPDATE / ON DELETE	Defines what happens when the parent row changes

Referential Actions Explained

When a parent row is updated or deleted, MySQL can respond differently depending on the action you configure:

Action	What Happens	When to Use
RESTRICT (default)	Blocks the parent change if child rows exist	Use by default — safest option
CASCADE	Automatically applies the same change to child rows	Use when child rows should follow the parent (e.g., cascading deletes for temp data)
SET NULL	Sets the foreign key column in child rows to NULL	Use when the relationship is optional (child column must allow NULL)
NO ACTION	Same as RESTRICT in MySQL (checked immediately)	Avoid — use RESTRICT for clarity

---

Step-by-Step Examples

Example 1: Building a Customer-Order Relationship

This is the most common pattern — a parent table (customers) and a child table (orders).

Step 1: Create the parent table

CREATE TABLE customers (
    customer_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    full_name   VARCHAR(120) NOT NULL,
    email       VARCHAR(255) NOT NULL UNIQUE,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create the child table with a foreign key

CREATE TABLE orders (
    order_id     BIGINT AUTO_INCREMENT PRIMARY KEY,
    customer_id  BIGINT NOT NULL,
    total_amount DECIMAL(12,2) NOT NULL,
    status       VARCHAR(20) NOT NULL DEFAULT 'pending',
    order_date   DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES customers(customer_id)
        ON UPDATE CASCADE
        ON DELETE RESTRICT
);

Step 3: Insert valid data

INSERT INTO customers (full_name, email) VALUES ('Alice Rahman', 'alice@example.com');
INSERT INTO orders (customer_id, total_amount) VALUES (1, 250.00);

Step 4: Try to insert an order for a non-existent customer

INSERT INTO orders (customer_id, total_amount) VALUES (999, 50.00);
-- ERROR 1452: Cannot add or update a child row:
-- a foreign key constraint fails

The foreign key blocks the insert because customer 999 doesn't exist. This is referential integrity in action.

Step 5: Try to delete a customer who has orders

DELETE FROM customers WHERE customer_id = 1;
-- ERROR 1451: Cannot delete or update a parent row:
-- a foreign key constraint fails

Because we used ON DELETE RESTRICT, MySQL prevents deleting a customer who still has orders.

---

Example 2: Composite Primary Key

Some tables need multiple columns to uniquely identify a row. For example, an order can contain multiple products:

CREATE TABLE order_items (
    order_id   BIGINT NOT NULL,
    product_id BIGINT NOT NULL,
    quantity   INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,

    PRIMARY KEY (order_id, product_id),

    CONSTRAINT fk_items_order
        FOREIGN KEY (order_id) REFERENCES orders(order_id)
        ON DELETE CASCADE,

    CONSTRAINT fk_items_product
        FOREIGN KEY (product_id) REFERENCES products(product_id)
        ON DELETE RESTRICT
);

Here, the combination of (order_id, product_id) must be unique — the same product can't appear twice in the same order. We use CASCADE on the order FK so that deleting an order automatically removes its line items.

---

Example 3: Adding a Foreign Key to an Existing Table

-- Add a FK to an existing table
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customer
    FOREIGN KEY (customer_id)
    REFERENCES customers(customer_id)
    ON DELETE RESTRICT;

-- Verify the FK was created
SHOW CREATE TABLE orders\G

---

Practical Use Cases

1. E-Commerce: Customers → Orders → Order Items

The classic three-table chain where customers place orders containing multiple products. Foreign keys prevent orphan orders and orphan line items.

2. Content Management: Users → Posts → Comments

Users write posts, and readers leave comments on posts. FKs ensure a comment always belongs to a real post and a real user.

3. HR Systems: Departments → Employees

Every employee belongs to a department. The FK prevents assigning an employee to a department that doesn't exist.

4. Multi-Tenant SaaS: Organizations → Projects → Tasks

Hierarchical ownership chains where deleting an organization should cascade through projects and tasks (or be blocked until they're archived).

---

Common Mistakes & Troubleshooting

Mistake	What Happens	How to Fix
Parent and child column types don't match	FK creation fails with a cryptic error	Match the exact data type, sign (UNSIGNED), and length
Forgetting an index on the FK column	Slow JOINs and slow constraint checks	MySQL auto-creates an index on FK columns, but verify with SHOW INDEX
Using CASCADE without thinking	Deleting one parent row wipes thousands of child rows	Default to RESTRICT, only use CASCADE when you've justified it
Creating FK before the parent table exists	Error during table creation	Create tables in dependency order (parent first)
Different storage engines	InnoDB supports FKs, MyISAM does not	Use InnoDB for all tables that need referential integrity

---

Best Practices

• Name your constraints — fk_orders_customer is debuggable, auto-generated names are not
• Default to RESTRICT — make cascade an intentional choice, not a default
• Match types exactly — if the parent PK is BIGINT UNSIGNED, the child FK must be too
• Always index FK columns — MySQL does this automatically for FKs, but check manually for older tables
• Use AUTO_INCREMENT for surrogate PKs — natural keys (like email) change; surrogate keys don't
• Document your relationships — keep an ER diagram updated with your schema

---

Hands-On Practice

Exercise 1: Build a Blog Schema (Easy)

Create three tables: authors, posts, and comments.

• authors has author_id (PK), name, email
• posts has post_id (PK), author_id (FK to authors), title, body
• comments has comment_id (PK), post_id (FK to posts), commenter_name, body

Test: Insert an author, a post, and a comment. Then try inserting a comment for a non-existent post.

Exercise 2: Fix a Type Mismatch (Medium)

Given these tables, explain why the FK fails and fix it:

CREATE TABLE departments (
    dept_id INT UNSIGNED PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    dept_id INT,  -- Notice: not UNSIGNED
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

Exercise 3: Choose the Right Referential Action (Advanced)

You have subscriptions and payment_history tables. A subscription can be cancelled, but payment records must be kept for legal compliance. Which ON DELETE action should you use on the FK from payment_history to subscriptions? Justify your answer.

---

Connection to Other Concepts

Related Concept	How It Connects
Column Constraints	NOT NULL, UNIQUE, and CHECK complement keys for full data integrity
Indexes	Foreign keys automatically create indexes; understanding indexing helps optimize FK performance
JOINs	FKs define the relationships that JOINs traverse
Transactions	FK checks happen within transaction boundaries
Schema Migrations	Adding/dropping FKs is a common migration step that requires careful ordering

What to Learn Next

• Column and Table Constraints — the other half of data integrity
• Index Strategy and Maintenance — optimize the indexes that FKs rely on

---

Visual Learning Diagram

erDiagram
    CUSTOMERS {
        BIGINT customer_id PK
        VARCHAR full_name
        VARCHAR email
    }
    ORDERS {
        BIGINT order_id PK
        BIGINT customer_id FK
        DECIMAL total_amount
        VARCHAR status
    }
    ORDER_ITEMS {
        BIGINT order_id PK,FK
        BIGINT product_id PK,FK
        INT quantity
    }
    PRODUCTS {
        BIGINT product_id PK
        VARCHAR name
        DECIMAL price
    }

    CUSTOMERS ||--o{ ORDERS : "places"
    ORDERS ||--o{ ORDER_ITEMS : "contains"
    PRODUCTS ||--o{ ORDER_ITEMS : "appears in"

Quick Reference

-- Create primary key
ALTER TABLE t ADD PRIMARY KEY (id);

-- Create foreign key
ALTER TABLE child ADD CONSTRAINT fk_name
    FOREIGN KEY (parent_id) REFERENCES parent(id)
    ON DELETE RESTRICT ON UPDATE CASCADE;

-- Drop foreign key
ALTER TABLE child DROP FOREIGN KEY fk_name;

-- Inspect table structure
SHOW CREATE TABLE child\G
SHOW INDEX FROM child;

What's Next

• Column and Table Constraints