Table Relationships Overview

A database becomes useful when its tables relate to each other. There are only three kinds of relationship, and every design is built from them.

How to find the relationship

Ask two questions, one in each direction:

1. “Can one A have many B?”
2. “Can one B have many A?”

Customer and order: a customer has many orders (yes). Does an order belong to many customers? No, to one. So it is one-to-many, and the foreign key sits in orders.

Student and course: a student takes many courses, and a course has many students. Many-to-many, so you need a link table.

Keys

• A primary key (PK) uniquely identifies a row in its own table.
• A foreign key (FK) is a column that holds the primary key of a row in another table. It is the link.

The golden rule: the foreign key lives on the “many” side. The row that can belong to only one parent stores that parent’s id.

1
2
3
4
5
6
7
8
9
10
11
12
13
14

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

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

INSERT INTO customers (name) VALUES ('Maya'), ('Rohan');
INSERT INTO orders (customer_id, total) VALUES (1, 100), (1, 50), (2, 70);

orders.customer_id points to customers.id. The FOREIGN KEY clause makes MySQL enforce the link:

1

INSERT INTO orders (customer_id, total) VALUES (99, 10);

1

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`learn`.`orders`, CONSTRAINT `fk_orders_customer` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`))

An order cannot refer to a customer who does not exist. That protection is called referential integrity (Foreign Keys).

Reading an ER diagram

An entity-relationship (ER) diagram draws tables as boxes and relationships as lines. The ends of a line show the cardinality. The common “crow’s foot” notation:

1
2

customers ||------o{ orders
   one customer  ->  zero or many orders

|| means “exactly one”, and o{ means “zero or many”. MySQL Workbench can draw this for you from an existing database (Database, then Reverse Engineer).

Mandatory or optional

For each side decide whether the relationship is required:

• customer_id INT NOT NULL: every order must have a customer.
• manager_id INT NULL: an employee may have no manager.

1
2
3

SELECT table_name, column_name, referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_schema = 'learn' AND referenced_table_name IS NOT NULL;

1
2
3
4
5
6

+------------+-------------+-----------------------+------------------------+
| TABLE_NAME | COLUMN_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+------------+-------------+-----------------------+------------------------+
| orders     | customer_id | customers             | id                     |
+------------+-------------+-----------------------+------------------------+
1 row in set

information_schema.key_column_usage lists the foreign keys of a database.

What comes next

The next lessons implement each relationship in detail, with sample data and queries: One-to-Many, One-to-One, Many-to-Many, Self-Referencing.