Связь «многие ко многим»

Магазин хочет помечать свои игры тегами, например «multiplayer», «family» или «story», чтобы покупателям было проще найти то, что им нравится. У игры может быть несколько тегов, а один тег относится ко многим играм. Это связь «многие ко многим», с которой вы познакомились в уроке про соединения таблиц, и она не вписывается в существующие таблицы магазина:

• Столбец tag_id в games может хранить только один тег для игры.
• Столбец tags со списком, например 'multiplayer, family', нельзя ни с чем соединить, база данных не может его проверить, а поиск по нему с помощью LIKE неудобен и находит лишнее.

Решение — третья таблица, соединительная, с одной строкой для каждой пары: у этой игры есть этот тег. В ней два внешних ключа, по одному на каждую таблицу, а пара является её первичным ключом. Поскольку ключ состоит из двух столбцов, он пишется после последнего столбца: PRIMARY KEY (game_id, tag_id). Тогда каждая игра может встречаться во многих строках, как и каждый тег, но одна и та же пара дважды встретиться не может.

Такие таблицы вы уже знаете. order_items соединяет заказы и игры (в заказе много игр, а игра входит во многие заказы), а reviews соединяет игры и клиентов. У обеих первичный ключ из двух столбцов, и обе показывают, что соединительная таблица может хранить о паре и другие данные: количество и цену или оценку и комментарий.

Вот то же самое для платформ, на которых работают игры: таблица платформ, соединительная таблица и запрос, соединяющий все три:

CREATE TABLE platforms (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
) STRICT;

CREATE TABLE game_platforms (
    game_id INTEGER NOT NULL REFERENCES games (id),
    platform_id INTEGER NOT NULL REFERENCES platforms (id),
    PRIMARY KEY (game_id, platform_id)
) STRICT;

INSERT INTO platforms (name)
VALUES ('PC'), ('Switch');

INSERT INTO game_platforms (game_id, platform_id)
VALUES (3, 1), (3, 2), (6, 1);

SELECT g.title, p.name AS platform
FROM game_platforms gp
JOIN games g ON g.id = gp.game_id
JOIN platforms p ON p.id = gp.platform_id
ORDER BY g.title, p.name;

Moon Farm работает на двух платформах, поэтому у неё две строки в game_platforms. Запрос начинается с соединительной таблицы и присоединяет каждый её внешний ключ к его таблице, как вы узнали в уроке про соединения.

Типичная ошибка: написать PRIMARY KEY после обоих столбцов. Получится два первичных ключа, а в таблице он может быть только один:

CREATE TABLE game_platforms (
    game_id INTEGER PRIMARY KEY,
    platform_id INTEGER PRIMARY KEY
) STRICT;

Error on line 1: table "game_platforms" has more than one primary key