Пошук уроків, статей та іншого контенту
Створите проміжну таблицю та реалізуєте зв’язок багато-до-багатьох у PostgreSQL.
Зв’язок Many-to-Many («багато-до-багатьох») означає, що:
один запис першої таблиці може бути пов’язаний із багатьма записами другої;
один запис другої таблиці може бути пов’язаний із багатьма записами першої.
Наприклад:
один студент може записатися на багато курсів;
один курс можуть проходити багато студентів.
Безпосередньо зберегти такий зв’язок між двома таблицями незручно. Для цього створюють проміжну таблицю. Вона містить по одному зовнішньому ключу на кожну з основних таблиць.
Схема матиме такий вигляд:
students ← student_courses → coursesПроміжна таблиця
Спочатку створимо таблиці students і courses:
CREATE TABLE students (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name text NOT NULL
);
CREATE TABLE courses (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL
);Кожна таблиця має власний первинний ключ id.
Тепер створимо проміжну таблицю:
CREATE TABLE student_courses (
student_id integer NOT NULL,
course_id integer NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id)
REFERENCES students (id),
FOREIGN KEY (course_id)
REFERENCES courses (id)
);У таблиці student_courses:
student_id посилається на students.id;
course_id посилається на courses.id;
пара (student_id, course_id) є первинним ключем.
Складений первинний ключ не дозволяє додати один і той самий зв’язок двічі. Наприклад, студент не зможе двічі бути записаний на той самий курс.
Додамо кілька студентів і курсів:
INSERT INTO students (full_name)
VALUES
('Олена Коваль'),
('Андрій Мельник'),
('Марія Бондар');
INSERT INTO courses (title)
VALUES
('PostgreSQL для початківців'),
('JavaScript'),
('HTML і CSS');Оскільки ідентифікатори генеруються автоматично, у прикладі вони матимуть значення від 1 до 3.
Тепер створимо зв’язки між студентами та курсами:
INSERT INTO student_courses (student_id, course_id)
VALUES
(1, 1),
(1, 2),
(2, 1),
(2, 3),
(3, 1),
(3, 2),
(3, 3);Після цього:
Олена записана на PostgreSQL і JavaScript;
Андрій записаний на PostgreSQL і HTML і CSS;
Марія записана на всі три курси.
Щоб отримати список студентів разом із курсами, потрібно об’єднати три таблиці за допомогою JOIN:
SELECT
students.full_name,
courses.title
FROM students
JOIN student_courses
ON student_courses.student_id = students.id
JOIN courses
ON courses.id = student_courses.course_id
ORDER BY students.full_name, courses.title;Результат матиме приблизно такий вигляд:
Андрій Мельник | HTML і CSS
Андрій Мельник | PostgreSQL для початківців
Марія Бондар | HTML і CSS
Марія Бондар | JavaScript
Марія Бондар | PostgreSQL для початківців
Олена Коваль | JavaScript
Олена Коваль | PostgreSQL для початківцівПорядок рядків залежить від умови ORDER BY.
Наприклад, знайдемо курси Олени:
SELECT courses.title
FROM courses
JOIN student_courses
ON student_courses.course_id = courses.id
JOIN students
ON students.id = student_courses.student_id
WHERE students.full_name = 'Олена Коваль';Знайдемо студентів, записаних на PostgreSQL:
SELECT students.full_name
FROM students
JOIN student_courses
ON student_courses.student_id = students.id
JOIN courses
ON courses.id = student_courses.course_id
WHERE courses.title = 'PostgreSQL для початківців';Щоб записати Андрія на JavaScript, достатньо додати рядок у проміжну таблицю:
INSERT INTO student_courses (student_id, course_id)
VALUES (2, 2);Основні таблиці при цьому не змінюються. Змінюється лише набір зв’язків у student_courses.
Якщо студент більше не проходить певний курс, можна видалити лише відповідний зв’язок:
DELETE FROM student_courses
WHERE student_id = 2
AND course_id = 2;Студент і курс залишаться у своїх таблицях. Видалиться тільки запис про їхній зв’язок.
За замовчуванням PostgreSQL не дозволить видалити студента або курс, якщо на нього посилаються рядки з student_courses.
Для автоматичного видалення пов’язаних рядків можна додати ON DELETE CASCADE:
CREATE TABLE student_courses (
student_id integer NOT NULL,
course_id integer NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id)
REFERENCES students (id)
ON DELETE CASCADE,
FOREIGN KEY (course_id)
REFERENCES courses (id)
ON DELETE CASCADE
);Тепер, якщо видалити студента, PostgreSQL автоматично видалить усі його зв’язки з курсами:
DELETE FROM students
WHERE id = 3;Курси при цьому не будуть видалені.
ON DELETE CASCADE потрібно використовувати обережно. Воно зручне для проміжних записів, які не мають сенсу без основного запису. Проте видалення основного рядка може автоматично видалити багато пов’язаних даних.
Нижче наведений повний приклад, який можна виконати в PostgreSQL:
DROP TABLE IF EXISTS student_courses;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS courses;
CREATE TABLE students (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name text NOT NULL
);
CREATE TABLE courses (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL
);
CREATE TABLE student_courses (
student_id integer NOT NULL,
course_id integer NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id)
REFERENCES students (id)
ON DELETE CASCADE,
FOREIGN KEY (course_id)
REFERENCES courses (id)
ON DELETE CASCADE
);
INSERT INTO students (full_name)
VALUES
('Олена Коваль'),
('Андрій Мельник'),
('Марія Бондар');
INSERT INTO courses (title)
VALUES
('PostgreSQL для початківців'),
('JavaScript'),
('HTML і CSS');
INSERT INTO student_courses (student_id, course_id)
VALUES
(1, 1),
(1, 2),
(2, 1),
(2, 3),
(3, 1),
(3, 2),
(3, 3);
SELECT
students.full_name,
courses.title
FROM students
JOIN student_courses
ON student_courses.student_id = students.id
JOIN courses
ON courses.id = student_courses.course_id
ORDER BY students.full_name, courses.title;Невдалий варіант:
student_id | course_ids
1 | '1,2,3'Такі дані складно перевіряти, фільтрувати та об’єднувати через JOIN.
Правильний варіант — окремий рядок для кожного зв’язку:
student_id | course_id
1 | 1
1 | 2
1 | 3Якщо не створити FOREIGN KEY, можна додати зв’язок із неіснуючим студентом або курсом:
INSERT INTO student_courses (student_id, course_id)
VALUES (999, 1);Зовнішній ключ не дозволить зберегти некоректне значення.
Якщо не встановити первинний або унікальний ключ для пари стовпців, один і той самий зв’язок можна буде додати кілька разів.
Для цього використовуйте:
PRIMARY KEY (student_id, course_id)Щоб скасувати запис студента на курс, потрібно видалити рядок із проміжної таблиці, а не самого студента або курсу:
DELETE FROM student_courses
WHERE student_id = 1
AND course_id = 2;Зв’язок Many-to-Many реалізують за допомогою проміжної таблиці.
Проміжна таблиця містить зовнішні ключі на обидві основні таблиці.
Кожен зв’язок зберігається окремим рядком.
Складений первинний ключ (student_id, course_id) забороняє дублікати.
Для отримання пов’язаних даних використовують JOIN.
ON DELETE CASCADE може автоматично видаляти зв’язки після видалення основного запису.