Database Design›Extra· 30 min read

Keys & Relationships

Primary and foreign keys — how tables connect into a well-designed database.

What you will learn

Use primary and foreign keys
Model one-to-many relationships

Primary & foreign keys

A primary key uniquely identifies each row (usually id).
A foreign key in one table points to a primary key in another, creating a link.

You declare a foreign key when you create the table. Here we build an orders table whose user_id column is tied to the id column of the users table, so every order is officially linked to a real user.

A foreign key linking orders to users

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT,
  total DECIMAL(8,2),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

Line by line: id INT AUTO_INCREMENT PRIMARY KEY is this table’s own primary key. user_id INT is a plain number column that will hold the id of the user who placed the order. total DECIMAL(8,2) stores the order amount with two decimal places (good for money). The last line, FOREIGN KEY (user_id) REFERENCES users(id), is the link — it tells MySQL that user_id must match an existing id in the users table.

Note: Output: Query OK, 0 rows affected. Like other CREATE TABLE commands, this builds structure rather than returning data. From now on MySQL enforces the link: trying to insert an order with a user_id that does not exist in users will be rejected with a foreign-key error.

Note: This models a one-to-many relationship: one user has many orders. Foreign keys keep data consistent (you cannot add an order for a user that does not exist).

Q. What does a foreign key do?

Answer: A foreign key references another table’s primary key, creating and enforcing a relationship between rows.

✍️ Practice

Add a foreign key linking two of your tables.
Model a “one author has many books” relationship.

🏠 Homework

Design a small e-commerce schema (users, orders, products) with keys.