MySQL One to Many: Foreign Key Design with Examples

One-to-many is the most common relationship. One parent row has many child rows, and each child belongs to exactly one parent.

Examples: a category has many products, a user has many posts, an order has many order lines.

Build it

query.sql

SQL

CREATE TABLE categories (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(40) NOT NULL UNIQUE
);

CREATE TABLE products (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NOT NULL,
    name VARCHAR(60) NOT NULL,
    price DECIMAL(8,2) NOT NULL,
    CONSTRAINT fk_products_category FOREIGN KEY (category_id) REFERENCES categories (id)
);

INSERT INTO categories (name) VALUES ('Stationery'), ('Bags'), ('Home');
INSERT INTO products (category_id, name, price) VALUES
    (1, 'Notebook', 80), (1, 'Gel Pen', 15.5), (2, 'Backpack', 999), (1, 'Marker Set', 145);

The foreign key is in products (the “many” side). Every product stores the id of its category.

Query across it

query.sql

SQL

SELECT c.name AS category, p.name AS product, p.price
FROM categories c
JOIN products p ON p.category_id = c.id
ORDER BY c.name, p.name;

Output

OUTPUT

+------------+------------+--------+
| category   | product    | price  |
+------------+------------+--------+
| Bags       | Backpack   | 999.00 |
| Stationery | Gel Pen    |  15.50 |
| Stationery | Marker Set | 145.00 |
| Stationery | Notebook   |  80.00 |
+------------+------------+--------+
4 rows in set

And per parent, including parents with no children:

query.sql

SQL

SELECT c.name AS category, COUNT(p.id) AS products, COALESCE(MAX(p.price), 0) AS most_expensive
FROM categories c
LEFT JOIN products p ON p.category_id = c.id
GROUP BY c.id, c.name
ORDER BY c.name;

Output

OUTPUT

+------------+----------+----------------+
| category   | products | most_expensive |
+------------+----------+----------------+
| Bags       |        1 |         999.00 |
| Home       |        0 |           0.00 |
| Stationery |        3 |         145.00 |
+------------+----------+----------------+
3 rows in set

The foreign key has an index

When you declare a foreign key, InnoDB makes sure that the column is indexed. If there is no suitable index, it creates one:

query.sql

SQL

SHOW INDEX FROM products;

Output

OUTPUT

+----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name             | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| products |          0 | PRIMARY              |            1 | id          | A         |           4 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| products |          1 | fk_products_category |            1 | category_id | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+----------+------------+----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set

The fk_products_category index is there. (The Cardinality column is an estimate and can differ between servers.) It makes joins fast, and also makes the checks on DELETE of a category fast. (Without it, deleting a category would scan the whole products table.)

What happens when you delete a parent?

By default, MySQL refuses to delete a parent that still has children:

query.sql

SQL

DELETE FROM categories WHERE name = 'Bags';

Output

OUTPUT

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`learn`.`products`, CONSTRAINT `fk_products_category` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`))

You can choose a different behaviour with ON DELETE:

Rule	Effect when the parent row is deleted
`RESTRICT` / `NO ACTION` (default)	The delete is refused
`CASCADE`	The children are deleted too
`SET NULL`	The child’s foreign key becomes `NULL` (the column must allow `NULL`)

query.sql

SQL

CREATE TABLE posts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    category_id INT UNSIGNED NULL,
    title VARCHAR(40) NOT NULL,
    CONSTRAINT fk_posts_category FOREIGN KEY (category_id) REFERENCES categories (id) ON DELETE SET NULL
);
INSERT INTO posts (category_id, title) VALUES (3, 'Decorate your desk'), (3, 'Lamp buying guide');

DELETE FROM categories WHERE name = 'Home';
SELECT id, category_id, title FROM posts ORDER BY id;

Output

OUTPUT

+----+-------------+--------------------+
| id | category_id | title              |
+----+-------------+--------------------+
|  1 |        NULL | Decorate your desk |
|  2 |        NULL | Lamp buying guide  |
+----+-------------+--------------------+
2 rows in set

The category was deleted, and the posts stayed, with category_id = NULL. Foreign Keys covers each rule and when to use it.

Choosing the rule

Children that cannot live without the parent (order lines without an order): ON DELETE CASCADE.
Children that should survive (posts without a category): ON DELETE SET NULL.
Important records that must not disappear by accident (orders for a customer): RESTRICT, so that you must deal with the orders explicitly.

Common design mistakes

Storing the child ids in the parent (categories.product_ids = '1,2,4'). That breaks the rules of relational design. Keep the foreign key in the child.
A different type on the two sides. INT UNSIGNED and BIGINT do not match. A foreign key needs the same type and sign as the key it references.
No foreign key at all (“the application handles it”). Applications have bugs. The database can guarantee integrity.
Allowing NULL in a foreign key when the relationship is required.

lightbulb

The foreign key goes on the many side

If you are unsure where to put the column, ask: “Which row can belong to only one of the other?” That row stores the id.