Understanding Relationships in MySQL: Primary and Foreign Keys

How primary and foreign keys link tables together and model one-to-many and many-to-many connections.

Primary keys

A primary key is the unique identifier for each row in a table – the column (or set of columns) that makes one row distinct from another.

Defining a primary key

When you create a table:

CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    PRIMARY KEY (customer_id)
);

customer_id uniquely identifies each customer.

What primary keys require

1. Uniqueness: every value must be different.
2. Not NULL: primary keys cannot be empty.
3. Stability: once assigned, the value should not change.

Foreign keys

Foreign keys turn a pile of separate tables into a relational database by linking rows in one table to rows in another.

Creating a foreign key

An orders table that references customers:

CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    order_date DATE,
    customer_id INT,
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

customer_id in orders references the primary key in customers.

What foreign keys give you

1. Referential integrity: you cannot link an order to a customer that does not exist.
2. Navigation: easy to move between related data in different tables.
3. Cascading actions: updates or deletions can propagate from one table to another if configured.

Relationship types

One-to-many

The most common pattern. One customer can have many orders:

-- Customers table
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    email VARCHAR(100),
    PRIMARY KEY (customer_id)
);

-- Orders table
CREATE TABLE orders (
    order_id INT AUTO_INCREMENT,
    order_date DATE,
    customer_id INT,
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

Many-to-many

These usually need a junction table. A customer can have many products, and each product can belong to many customers:

-- Products table
CREATE TABLE products (
    product_id INT AUTO_INCREMENT,
    name VARCHAR(100),
    price DECIMAL(10, 2),
    PRIMARY KEY (product_id)
);

-- Customer_Products junction table
CREATE TABLE customer_products (
    customer_id INT,
    product_id INT,
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (product_id) REFERENCES products(product_id)
);

A few practical tips

1. Choose primary keys carefully: stable, unique, and not overly complex.
2. Use foreign keys for integrity: they enforce relationships between tables.
3. Index foreign keys: this improves JOIN performance.
4. Understand cascading: if cascading deletes or updates are configured, know what they will do before you rely on them.

Wrapping up

Primary and foreign keys are how relational databases stay organised. Get them right at design time and your tables will link together cleanly when you start writing queries.