Моделирование связи MANY-TO-MANY с помощью промежуточной таблицы

Связь "многие-ко-многим" — это когда одна запись в одной таблице может быть связана с несколькими записями в другой таблице, и наоборот. Например: - Один студент (из таблицы students) может быть записан на несколько курсов (из таблицы courses). - Один курс может быть посещён несколькими студентами.

Проблема в том, что напрямую такую связь хранить неудобно. Поэтому на помощь приходит промежуточная таблица, которая хранит эти связи.

Пример из реальной жизни

Представьте, что вы завели таблицу студентов и таблицу курсов. Если вы попытаетесь добавить в каждую таблицу колонку для хранения всех связанных данных, у вас будет хаос:

• В таблице students придётся хранить список всех курсов, на которые записан студент. Но как хранить список? Через запятую? В массиве? Это будет сложно для обработки запросов.
• В таблице courses придётся хранить список студентов, а это вообще вызывает головную боль.

Поэтому правильное решение — создать третью таблицу, которая будет хранить связи между студентами и курсами.

Промежуточная таблица: наше спасение!

Промежуточная таблица (иногда её называют таблицей связей) решает все проблемы. Она содержит две внешние ссылки:

• Внешний ключ на таблицу students.
• Внешний ключ на таблицу courses.

Каждая запись в этой таблице создаёт связь между конкретным студентом и конкретным курсом.

Создание таблиц для связи "многие-ко-многим"

Давайте практиковаться! Вот как мы можем создать таблицы для связей между студентами и курсами:

Шаг 1: Создание таблицы students

Вот наша таблица для студентов. Здесь мы храним уникальные идентификаторы студентов и их имена.

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

• student_id — это уникальный идентификатор студента (автоинкрементируемый, спасибо SERIAL!).
• name — имя студента.

Шаг 2: Создание таблицы courses

Теперь создадим таблицу для курсов. Здесь мы храним уникальные идентификаторы курсов и их названия.

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

• course_id — уникальный идентификатор курса.
• title — название курса.

Шаг 3: Создание промежуточной таблицы enrollments

Теперь создадим нашу волшебную таблицу-связку. Она содержит две колонки, каждая из которых является внешним ключом, указывающим на соответствующие таблицы.

CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

Разберём структуру:

• student_id — внешний ключ, ссылающийся на student_id из таблицы students.
• course_id — внешний ключ, ссылающийся на course_id из таблицы courses.
• PRIMARY KEY (student_id, course_id) — первичный ключ состоит из комбинации двух внешних ключей. Это гарантирует, что каждая связь будет уникальной.

Вставка данных

Давайте добавим немного данных, чтобы проверить, как всё работает.

Шаг 1: Добавление студентов

INSERT INTO students (name) VALUES
('Alice'),
('Bob'),
('Charlie');

Результат:

Шаг 2: Добавление курсов

INSERT INTO courses (title) VALUES
('Mathematics'),
('History'),
('Biology');

Результат:

Шаг 3: Добавление записей в enrollments

Теперь запишем студентов на курсы. Например:

• Alice записана на Mathematics и History.
• Bob записан только на Biology.
• Charlie записан на все три курса.

INSERT INTO enrollments (student_id, course_id) VALUES
(1, 1), -- Alice на Mathematics
(1, 2), -- Alice на History
(2, 3), -- Bob на Biology
(3, 1), -- Charlie на Mathematics
(3, 2), -- Charlie на History
(3, 3); -- Charlie на Biology

Результат:

Запросы для связи "многие-ко-многим"

Теперь, когда у нас есть данные, пора получить от них пользу!

Как найти все курсы, на которые записан студент?

Например, чтобы узнать, на какие курсы записан Alice (ID = 1), выполните запрос:

SELECT c.title
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
WHERE e.student_id = 1;

Результат:

Как найти всех студентов, записанных на конкретный курс?

Допустим, мы хотим узнать, кто записан на курс Mathematics (ID = 1):

SELECT s.name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE e.course_id = 1;

Результат:

Как найти студентов и их курсы?

Чтобы получить полную картину, кто на что записан, выполним запрос:

SELECT s.name AS student, c.title AS course
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id;

Результат:

Таблица enrollments делает нашу схему гибкой — мы можем без проблем добавлять или удалять связи между студентами и курсами, не трогая основные таблицы. Благодаря JOIN-запросам легко находить нужные данные, например, кто записан на какой курс. А внешние ключи автоматически следят за тем, чтобы не возникало ошибок — например, чтобы никто случайно не записал студента на курс, которого вообще не существует.

Типичные ошибки в связях "многие-ко-многим"

Отсутствие уникального ограничения: Если не установить PRIMARY KEY, можно случайно добавить одну и ту же связь несколько раз.

Нарушение целостности данных: Попытка вставить запись с несуществующим student_id или course_id приведёт к ошибке.

Неправильный порядок удаления данных: Если сначала удалить курс из courses, записи в enrollments останутся "сиротами". Для предотвращения этого используйте ON DELETE CASCADE в определении внешних ключей.

2

Задача

SQL SELF, 19 уровень, 4 лекция

Недоступна

Создание таблиц для связи "многие ко многим"

Создание таблиц для связи "многие ко многим"

Открыть

2

Задача

SQL SELF, 19 уровень, 4 лекция

Недоступна

Получение связей между пользователями и проектами

Получение связей между пользователями и проектами

Открыть

1

Опрос

Внешние ключи, 19 уровень, 4 лекция

Недоступен

Внешние ключи

Внешние ключи

Открыть