Пошук уроків, статей та іншого контенту
Розберете зв’язок один-до-багатьох, зовнішні ключі та типові сценарії його використання.
Зв’язок One-to-Many означає, що одному запису в першій таблиці можуть відповідати багато записів у другій таблиці.
Приклади:
один користувач має багато замовлень;
одна категорія містить багато товарів;
один автор написав багато книг;
один клієнт може мати багато звернень до служби підтримки.
У такому зв’язку є дві сторони:
одна — батьківська таблиця;
багато — дочірня таблиця.
Наприклад, для зв’язку «категорія — товари»:
таблиця categories — сторона «один»;
таблиця products — сторона «багато».
Важливо: зовнішній ключ розташовується саме в таблиці зі стороною «багато».
Батьківська таблиця зазвичай має первинний ключ:
CREATE TABLE categories (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);У дочірній таблиці створюється стовпчик із зовнішнім ключем:
CREATE TABLE products (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
category_id integer NOT NULL REFERENCES categories(id)
);Стовпчик products.category_id посилається на categories.id.
Це означає:
кожен товар повинен належати до наявної категорії;
не можна вказати category_id, якого немає в categories;
значення зовнішнього ключа може повторюватися для різних товарів.
Наприклад, кілька товарів можуть мати однакове значення category_id:
categories
id | name
---+------------
1 | Книги
2 | Електроніка
products
id | name | category_id
---+-------------------+------------
1 | SQL для початківців | 1
2 | PostgreSQL Guide | 1
3 | Навушники | 2Два перші товари належать категорії з id = 1.
Розглянемо повний приклад із авторами та книгами.
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;
CREATE TABLE authors (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE books (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
published_year integer,
author_id integer NOT NULL,
CONSTRAINT books_author_id_fkey
FOREIGN KEY (author_id)
REFERENCES authors(id)
);Тут:
authors.id — первинний ключ авторів;
books.author_id — зовнішній ключ;
один автор може мати багато книг;
кожна книга повинна мати автора.
Обмеження NOT NULL для author_id забороняє створювати книгу без автора.
Спочатку потрібно додати записи до батьківської таблиці:
INSERT INTO authors (name)
VALUES
('Тарас Шевченко'),
('Леся Українка'),
('Іван Франко');Після цього можна додавати книги, використовуючи наявні ідентифікатори авторів:
INSERT INTO books (title, published_year, author_id)
VALUES
('Кобзар', 1840, 1),
('Гайдамаки', 1841, 1),
('Лісова пісня', 1911, 2),
('Захар Беркут', 1883, 3);Якщо спробувати додати книгу з неіснуючим автором, PostgreSQL відхилить операцію:
INSERT INTO books (title, published_year, author_id)
VALUES ('Невідома книга', 2020, 999);З’явиться помилка порушення зовнішнього ключа, оскільки автора з id = 999 немає.
SELECT *
FROM books;Цей запит поверне author_id, але не ім’я автора.
Щоб отримати повну інформацію, потрібно об’єднати таблиці за допомогою JOIN.
SELECT
books.title,
books.published_year,
authors.name AS author_name
FROM books
JOIN authors ON authors.id = books.author_id;Результат матиме приблизно такий вигляд:
title | published_year | author_name
------------------+----------------+----------------
Кобзар | 1840 | Тарас Шевченко
Гайдамаки | 1841 | Тарас Шевченко
Лісова пісня | 1911 | Леся Українка
Захар Беркут | 1883 | Іван ФранкоУмова:
authors.id = books.author_idпорівнює первинний ключ автора із зовнішнім ключем книги.
SELECT
books.title,
books.published_year
FROM books
JOIN authors ON authors.id = books.author_id
WHERE authors.name = 'Тарас Шевченко';Або за ідентифікатором автора:
SELECT
title,
published_year
FROM books
WHERE author_id = 1;Фільтрувати за ідентифікатором зазвичай надійніше, якщо він уже відомий.
Щоб дізнатися, скільки книг написав кожен автор, використовуйте COUNT:
SELECT
authors.name,
COUNT(books.id) AS books_count
FROM authors
LEFT JOIN books ON books.author_id = authors.id
GROUP BY authors.id, authors.name
ORDER BY authors.name;LEFT JOIN потрібен для того, щоб у результаті залишилися також автори, у яких поки немає книг.
Якщо використати звичайний JOIN, автори без книг не потраплять до результату.
Спочатку можна знайти автора:
SELECT id, name
FROM authors
WHERE name = 'Леся Українка';Потім додати книгу з отриманим id:
INSERT INTO books (title, published_year, author_id)
VALUES ('Камінний господар', 1912, 2);Книгу можна перемістити до іншого автора, змінивши зовнішній ключ:
UPDATE books
SET author_id = 3
WHERE title = 'Камінний господар';Після цього книга буде пов’язана з автором, у якого id = 3.
Оскільки author_id має зовнішній ключ, PostgreSQL перевірить, чи існує такий автор.
За замовчуванням PostgreSQL не дозволяє видалити автора, якщо в нього є книги:
DELETE FROM authors
WHERE id = 1;Якщо для цього автора існують записи в books, операція завершиться помилкою. Це захищає дані від появи книг із неіснуючим автором.
Потрібну поведінку можна визначити в зовнішньому ключі за допомогою ON DELETE.
ON DELETE RESTRICTЦе поведінка за замовчуванням: не дозволяти видалення батьківського запису, якщо дочірні записи ще існують.
CREATE TABLE books (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
author_id integer NOT NULL,
FOREIGN KEY (author_id)
REFERENCES authors(id)
ON DELETE RESTRICT
);Такий варіант підходить, коли дочірні записи не можна втрачати автоматично.
ON DELETE CASCADEПри видаленні автора PostgreSQL автоматично видалить усі його книги:
CREATE TABLE books (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
author_id integer NOT NULL,
FOREIGN KEY (author_id)
REFERENCES authors(id)
ON DELETE CASCADE
);CASCADE зручно використовувати, коли дочірні записи не мають сенсу без батьківського запису. Наприклад, для позицій замовлення після видалення самого замовлення.
Використовувати CASCADE потрібно обережно: одна операція DELETE може видалити багато пов’язаних записів.
ON DELETE SET NULLЗовнішній ключ можна обнулити після видалення батьківського запису:
CREATE TABLE books (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
author_id integer,
FOREIGN KEY (author_id)
REFERENCES authors(id)
ON DELETE SET NULL
);У цьому випадку author_id не повинен мати обмеження NOT NULL.
Такий варіант підходить, якщо книгу потрібно зберегти, навіть коли автора видалено.
Нижче наведений скрипт можна виконати в PostgreSQL послідовно:
DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;
CREATE TABLE authors (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE books (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
title text NOT NULL,
published_year integer,
author_id integer NOT NULL,
CONSTRAINT books_author_id_fkey
FOREIGN KEY (author_id)
REFERENCES authors(id)
ON DELETE RESTRICT
);
INSERT INTO authors (name)
VALUES
('Тарас Шевченко'),
('Леся Українка'),
('Іван Франко');
INSERT INTO books (title, published_year, author_id)
VALUES
('Кобзар', 1840, 1),
('Гайдамаки', 1841, 1),
('Лісова пісня', 1911, 2),
('Захар Беркут', 1883, 3);
-- Отримати всі книги разом з іменами авторів
SELECT
books.title,
books.published_year,
authors.name AS author_name
FROM books
JOIN authors ON authors.id = books.author_id
ORDER BY authors.name, books.title;
-- Порахувати кількість книг кожного автора
SELECT
authors.name,
COUNT(books.id) AS books_count
FROM authors
LEFT JOIN books ON books.author_id = authors.id
GROUP BY authors.id, authors.name
ORDER BY authors.name;
-- Знайти книги Тараса Шевченка
SELECT
books.title,
books.published_year
FROM books
JOIN authors ON authors.id = books.author_id
WHERE authors.name = 'Тарас Шевченко';Зв’язок один-до-багатьох часто використовується в таких моделях:
users і orders — один користувач має багато замовлень;
orders і order_items — одне замовлення має багато позицій;
categories і products — одна категорія має багато товарів;
departments і employees — один відділ має багато працівників;
posts і comments — один допис має багато коментарів.
У кожному випадку зовнішній ключ розміщується в таблиці, де можуть бути багато записів:
users.id ← orders.user_id
categories.id ← products.category_id
posts.id ← comments.post_idPostgreSQL не створює індекс на зовнішньому ключі автоматично. Якщо за зовнішнім ключем часто шукають пов’язані записи, індекс варто створити вручну:
CREATE INDEX books_author_id_idx
ON books(author_id);Такий індекс може прискорити запити на кшталт:
SELECT *
FROM books
WHERE author_id = 1;Для невеликих таблиць різниця може бути непомітною, але для великих таблиць індекс стає важливим.
У зв’язку «один автор — багато книг» author_id має бути в таблиці books, а не в authors.
Неправильно було б зберігати список усіх книг в одному стовпчику автора. Реляційна модель використовує окремий рядок для кожної книги.
Якщо спочатку додати книгу, а потім автора, зовнішній ключ не зможе пройти перевірку.
Правильний порядок:
створити автора;
отримати його id;
створити книгу із цим id.
INSERT INTO books (title, author_id)
VALUES ('Нова книга', 500);Якщо автора з id = 500 немає, PostgreSQL відхилить запит.
CASCADEON DELETE CASCADE може видалити всі дочірні записи разом із батьківським. Перед його використанням потрібно переконатися, що така поведінка справді потрібна.
JOIN замість LEFT JOINJOIN не показує батьківські записи без дочірніх:
SELECT
authors.name,
COUNT(books.id)
FROM authors
JOIN books ON books.author_id = authors.id
GROUP BY authors.id, authors.name;Якщо потрібно показати також авторів без книг, використовуйте LEFT JOIN.
Зв’язок One-to-Many означає, що одному запису відповідає багато записів.
Таблиця зі стороною «один» має первинний ключ.
Таблиця зі стороною «багато» має зовнішній ключ.
Зовнішній ключ гарантує, що пов’язаний батьківський запис існує.
Для отримання даних із двох таблиць використовують JOIN.
LEFT JOIN дає змогу побачити батьківські записи без дочірніх.
ON DELETE визначає поведінку під час видалення батьківського запису.
Зовнішній ключ часто варто індексувати для швидшого пошуку.