SQL: две таблицы, JOIN и внешние ключи

Зачем это нужно

В прошлом уроке была одна таблица, и всё было просто: строка — это статья.

Теперь к статье приходят комментарии. Их у одной статьи может быть ноль, а может пятьдесят, и заранее вы этого не знаете. Класть их в ту же строку некуда: колонка comment1, comment2, comment3 — это не схема, а тупик.

Правильный ответ — вторая таблица, которая ссылается на первую. Сегодня разберём, как её связать, как собрать обе обратно в один ответ и что случится с комментариями, когда статью удалят.

Сразу целиком

Открывайте тот же blog.db, что и в прошлом уроке, — с теми же тремя строками настройки:

.mode box
.headers on
pragma foreign_keys = on;

Вторая таблица:

create table comments (
    id         integer primary key,
    article_id integer not null references articles(id) on delete cascade,
    author     text    not null,
    body       text    not null
) strict;

Ключевая строка здесь одна, и её мы разберём первой.

Разбор

Вторая таблица ссылается на первую

Комментарии не лежат внутри статьи. Они лежат отдельной таблицей и ссылаются на статью числом:

article_id integer not null references articles(id) on delete cascade

Это и есть внешний ключ: в comments.article_id лежит articles.id. references говорит базе, что чужих номеров там быть не должно, а on delete cascade — что вместе со статьёй уходят и её комментарии.

Комментариев у нас пока нет — добавим три, к первой статье и ко второй:

insert into comments (article_id, author, body) values
  (1, 'Асель', 'Хорошая статья'),
  (1, 'Дана',  'Спасибо'),
  (2, 'Асель', 'А про купол будет?');

join: только те, у кого есть пара

Собрать комментарии вместе со статьями — работа join:

select a.title, c.author, c.body
from articles a
join comments c on c.article_id = a.id
order by a.id, c.id;

┌───────────────────┬────────┬────────────────────┐
│       title       │ author │        body        │
├───────────────────┼────────┼────────────────────┤
│ О степи           │ Асель  │ Хорошая статья     │
│ О степи           │ Дана   │ Спасибо            │
│ Что такое шанырак │ Асель  │ А про купол будет? │
└───────────────────┴────────┴────────────────────┘

join оставляет только те пары, для которых нашлось совпадение. Статьи без комментариев в такой ответ не попадут — а на главной странице они нужны, пусть и с нулём.

left join: и те, у кого пары нет

Он берёт все строки левой таблицы, а справа подставляет пусто, если пары нет.

select a.title, count(c.id) as комментариев
from articles a
left join comments c on c.article_id = a.id
group by a.id, a.title
order by комментариев desc, a.id;

┌───────────────────┬──────────────┐
│       title       │ комментариев │
├───────────────────┼──────────────┤
│ О степи           │ 2            │
│ Что такое шанырак │ 1            │
│ Привет, мир       │ 0            │
│ Черновик          │ 0            │
└───────────────────┴──────────────┘

count(c.id) считает комментарии, group by собирает строки в группы по статье. Обратите внимание на нули у двух последних: обычный join их бы просто не показал.

Ловушка SQLite: внешние ключи выключены

Удалим статью и проверим, ушли ли с ней комментарии:

delete from articles where slug = 'dala';
select count(*) as осталось_комментариев from comments;

┌───────────────────────┐
│ осталось_комментариев │
├───────────────────────┤
│ 1                     │
└───────────────────────┘

Было три комментария, два относились к удалённой статье — остался один. on delete cascade сработал.

Сработал он потому, что в самом начале мы написали:

pragma foreign_keys = on;

Без этой строки не работает ничего из написанного выше про ключи. Проверьте на новом подключении:

pragma foreign_keys;

┌──────────────┐
│ foreign_keys │
├──────────────┤
│ 0            │
└──────────────┘

Ноль. SQLite по историческим причинам держит проверку внешних ключей выключенной по умолчанию, и включать её надо в каждом соединении заново. Молча, без ошибки: references будет записан в схему и не будет проверяться, а on delete cascade оставит комментарии сиротами.

Это первое, что вы сделаете в следующем уроке, когда к базе подключится Go.

Карта урока

Скажите своими словами

Не подглядывая, ответьте вслух или на бумаге. Ответы — в конце урока.

1. Почему комментарии лежат отдельной таблицей, а не колонками в articles?
2. Чем join отличается от left join и когда разница видна?
3. Что произойдёт с on delete cascade, если забыть pragma foreign_keys = on?

Задание

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

По желанию.

• Вставьте комментарий к статье с несуществующим номером и прочитайте, что скажет база. Потом выключите pragma foreign_keys и повторите.
• Замените left join на join в запросе со счётом и объясните, куда делись две строки.
• Удалите статью, у которой комментариев нет, и убедитесь, что число комментариев не изменилось.

Куда это встанет в блоге

Схема из двух уроков и есть схема блога. В следующем уроке к ней подключится Go: database/sql, драйвер и первая строка, прочитанная из файла, а не из среза.

Долги. Запросы мы писали руками в консоли — из Go их придётся отправлять так, чтобы чужой текст не мог стать частью команды; это урок про инъекции. И pragma foreign_keys придётся включать в каждом соединении, а соединений у программы будет несколько — про это следующий урок и говорит отдельно.

Ответы

Источники

• SQLite: внешние ключи
• SQLite: язык запросов
• SQLite: строгие таблицы
• Учебник PostgreSQL: SQL с нуля