Primary Keys, Foreign Keys, and Why Relationships Matter

Back in lesson one, you saw orders referencing customers through a customer_id number. It's time to make that relationship formal and understand exactly why it matters.

What A Primary Key Is

A primary key uniquely identifies every row in a table; no two rows can ever share the same primary key value, and it can never be NULL. You've been using INT PRIMARY KEY AUTO_INCREMENT already, which is the most common pattern: a simple incrementing number with no business meaning, often called a surrogate key.

What A Foreign Key Is

A foreign key is a column in one table that references the primary key of another table. It's how MySQL formally understands that orders.customer_id points to customers.id, rather than that connection existing only in your head or in application code:

CREATE TABLE customers (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    total DECIMAL(10,2) NOT NULL,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

That FOREIGN KEY line is a constraint, a rule MySQL actively enforces. Try inserting an order with a customer_id that doesn't exist in customers, and MySQL rejects it with an error, rather than silently storing broken, orphaned data. This is the single biggest practical reason to declare foreign keys explicitly instead of just trusting your application code to always get it right: MySQL becomes a second line of defense against bugs.

Controlling Deletes With On Delete

Foreign keys also control what happens on deletion, through ON DELETE behavior. Without specifying it, MySQL defaults to RESTRICT, meaning it refuses to delete a customer row while orders still reference it, protecting you from accidentally orphaning data:

FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE

ON DELETE CASCADE instead says: if a customer is deleted, automatically delete all their orders too. This is powerful but genuinely dangerous if misused, think carefully about whether cascading deletion is really the business rule you want, versus ON DELETE RESTRICT (block the delete) or ON DELETE SET NULL (keep the order, just blank out customer_id). A beginner mistake is adding CASCADE everywhere by habit without thinking it through, then being shocked when deleting one row silently wipes out large chunks of related data.

One To Many And Many To Many

Relationships come in three shapes. One-to-many is what you've seen: one customer, many orders. Many-to-many needs a third table in between, called a pivot or junction table. Picture products and orders, where one order can contain many products, and one product can appear in many orders:

CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(id),
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Notice the primary key here is the combination of both columns together, called a composite key, since neither order_id nor product_id alone is unique, but the pair is. One-to-one relationships are rarer, used when you'd otherwise have a table with many optional, loosely related columns, like splitting a huge users table into users and user_profiles, connected one-to-one.

A frequent beginner mistake is forgetting to index foreign key columns. MySQL often adds an index automatically when you declare a FOREIGN KEY constraint, but if you ever add the relationship logic only in your application code without the actual constraint, you must remember to index that column yourself, or every JOIN using it (the subject of the next lesson) will be slow on anything beyond a tiny table.

A practical tip: name foreign key columns consistently, singular_table_name_id, like customer_id, product_id. This convention alone makes a schema far easier to read, because anyone glancing at a column name instantly knows which table it points to.

Try It Yourself

Before you move on, try this: design a students table and a courses table, plus a pivot table enrollments connecting them many-to-many, including a enrolled_at DATETIME column on the pivot table itself to record when each enrollment happened.