Как реализуются отношения между таблицами в реляционных базах данных

Что проверяют

Для тестировщика это вопрос не про проектирование, а про то, сможете ли вы сами проверить данные: написать JOIN, найти «осиротевшие» записи, понять, почему не удаляется пользователь. Следом часто просят написать запрос или объяснить, что будет при удалении родительской записи.

Как отвечать

Связи строятся на ключах: первичный ключ (PRIMARY KEY) идентифицирует строку, внешний (FOREIGN KEY ... REFERENCES) ссылается на него из другой таблицы.

• один ко многим: самый частый случай. В таблице orders колонка user_id ссылается на users.id, у одного пользователя много заказов;
• один к одному: тот же внешний ключ, но с ограничением UNIQUE, например profiles.user_id. Без UNIQUE это уже один ко многим, как бы ни называлась таблица;
• многие ко многим: напрямую не выражается, нужна связующая таблица, например order_items с двумя внешними ключами и составным первичным ключом.

Дальше то, что важно именно для тестирования. Поведение при удалении задаётся в самом ключе: ON DELETE CASCADE удалит дочерние строки, SET NULL обнулит ссылку, а RESTRICT или NO ACTION (по умолчанию) не дадут удалить родителя. Это готовые тест-кейсы: удалить пользователя с заказами и проверить, что стало с заказами.

Для проверки целостности пригодится запрос на сироты: SELECT o.* FROM orders o LEFT JOIN users u ON u.id = o.user_id WHERE u.id IS NULL. Если внешнего ключа в схеме нет (так бывает ради скорости или между сервисами), такие строки появляются, и ловить их придётся вам. При подготовке данных помните порядок: сначала родитель, потом ребёнок, а чистить в обратном порядке.

Где спотыкаются

Называют связь один к одному, не упоминая UNIQUE. Интервьюер спросит, что мешает вставить второй профиль тому же пользователю, и ответа не будет.

Путают INNER JOIN и LEFT JOIN при поиске потерянных данных. INNER JOIN просто не покажет строки без пары, и проблема останется незамеченной.

Не допускают, что внешнего ключа может не быть. Уверенность «база не даст сохранить мусор» подводит в микросервисах, где связь держится только в коде приложения.