Пошук уроків, статей та іншого контенту
Навчитеся моделювати зв’язок один-до-одного та отримувати пов’язані записи між двома таблицями.
Зв’язок One-to-One означає, що одному запису в першій таблиці відповідає не більше одного запису в другій таблиці.
Наприклад:
один користувач має один профіль;
один працівник має одну перепустку;
один акаунт має одні налаштування.
Розглянемо зв’язок між таблицями users і profiles:
users
1 ───── 0..1
profilesЦе означає:
один користувач може мати один профіль;
профіль належить одному користувачу;
профіль може бути відсутнім, якщо його ще не створили.
Для зв’язку One-to-One потрібні:
первинний ключ у головній таблиці;
зовнішній ключ у пов’язаній таблиці;
обмеження UNIQUE для зовнішнього ключа.
Саме UNIQUE не дозволяє кільком записам пов’язуватися з одним записом головної таблиці.
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE
);У таблиці users поле id є первинним ключем. Воно однозначно ідентифікує кожного користувача.
CREATE TABLE profiles (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL UNIQUE,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
bio TEXT,
CONSTRAINT profiles_user_id_fkey
FOREIGN KEY (user_id)
REFERENCES users (id)
);Поле user_id виконує одразу дві ролі:
FOREIGN KEY гарантує, що користувач існує в таблиці users;
UNIQUE гарантує, що для одного користувача не буде двох профілів.
Наведений приклад можна виконати в PostgreSQL послідовно.
DROP TABLE IF EXISTS profiles;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id BIGSERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(255) NOT NULL UNIQUE
);
CREATE TABLE profiles (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL UNIQUE,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
bio TEXT,
CONSTRAINT profiles_user_id_fkey
FOREIGN KEY (user_id)
REFERENCES users (id)
);
INSERT INTO users (username, email)
VALUES
('olena', 'olena@example.com'),
('andrii', 'andrii@example.com'),
('marta', 'marta@example.com');
INSERT INTO profiles (user_id, first_name, last_name, bio)
VALUES
(1, 'Олена', 'Коваль', 'Розробниця програмного забезпечення'),
(2, 'Андрій', 'Шевченко', 'Вивчає PostgreSQL');У цьому прикладі:
користувачі з ідентифікаторами 1 і 2 мають профілі;
користувач із ідентифікатором 3 поки не має профілю.
Щоб отримати дані з двох таблиць, використовують JOIN.
INNER JOININNER JOIN повертає лише ті записи, для яких пов’язаний запис існує в обох таблицях.
SELECT
users.id,
users.username,
users.email,
profiles.first_name,
profiles.last_name,
profiles.bio
FROM users
INNER JOIN profiles
ON profiles.user_id = users.id;Результат міститиме Олену та Андрія, але не Марту, оскільки в неї ще немає профілю.
Для коротших запитів можна використовувати псевдоніми таблиць:
SELECT
u.username,
p.first_name,
p.last_name
FROM users AS u
JOIN profiles AS p
ON p.user_id = u.id;Якщо потрібно показати всіх користувачів, навіть тих, які ще не мають профілю, використовуйте LEFT JOIN.
SELECT
u.id,
u.username,
u.email,
p.first_name,
p.last_name,
p.bio
FROM users AS u
LEFT JOIN profiles AS p
ON p.user_id = u.id
ORDER BY u.id;Для Марти поля з таблиці profiles матимуть значення NULL:
id | username | email | first_name | last_name
---+----------+-------------------+------------+----------
1 | olena | olena@example.com | Олена | Коваль
2 | andrii | andrii@example.com | Андрій | Шевченко
3 | marta | marta@example.com | NULL | NULLРізниця між типами з’єднання:
INNER JOIN — лише записи з відповідностями в обох таблицях;
LEFT JOIN — усі записи з лівої таблиці та відповідні записи з правої.
Спочатку потрібно додати користувача, а потім профіль із його id.
INSERT INTO users (username, email)
VALUES ('taras', 'taras@example.com')
RETURNING id;RETURNING id повертає ідентифікатор щойно створеного користувача. Якщо PostgreSQL повернув 4, профіль можна створити так:
INSERT INTO profiles (user_id, first_name, last_name, bio)
VALUES (
4,
'Тарас',
'Бондар',
'Працює з базами даних'
);Щоб змінити дані профілю, використовуйте його власний первинний ключ або user_id.
UPDATE profiles
SET
bio = 'Вивчає проєктування баз даних'
WHERE user_id = 2;Щоб отримати змінений профіль:
SELECT
u.username,
p.first_name,
p.last_name,
p.bio
FROM users AS u
JOIN profiles AS p
ON p.user_id = u.id
WHERE u.id = 2;Зараз зовнішній ключ не має правила автоматичного видалення. Тому спроба видалити користувача, якщо в нього є профіль, завершиться помилкою:
DELETE FROM users
WHERE id = 1;Спочатку потрібно видалити профіль, а потім користувача:
DELETE FROM profiles
WHERE user_id = 1;
DELETE FROM users
WHERE id = 1;Якщо профіль повинен автоматично видалятися разом із користувачем, можна додати ON DELETE CASCADE:
CREATE TABLE profiles (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL UNIQUE,
first_name VARCHAR(50) NOT NULL,
last_name VARCHAR(50) NOT NULL,
bio TEXT,
CONSTRAINT profiles_user_id_fkey
FOREIGN KEY (user_id)
REFERENCES users (id)
ON DELETE CASCADE
);Тепер видалення користувача автоматично видалить і його профіль:
DELETE FROM users
WHERE id = 1;ON DELETE CASCADE потрібно використовувати обережно, адже видалення одного запису спричиняє автоматичне видалення пов’язаного запису.
Спробуємо створити другий профіль для того самого користувача:
INSERT INTO profiles (user_id, first_name, last_name)
VALUES (2, 'Інше ім’я', 'Інше прізвище');PostgreSQL відхилить операцію, оскільки user_id має обмеження UNIQUE.
Без цього обмеження база дозволила б створити кілька профілів для одного користувача. Тоді зв’язок став би One-to-Many, а не One-to-One.
UNIQUEТакий варіант створює зовнішній ключ, але не гарантує зв’язок One-to-One:
CREATE TABLE profiles (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
first_name VARCHAR(50) NOT NULL,
FOREIGN KEY (user_id)
REFERENCES users (id)
);У цій таблиці можна буде створити кілька профілів з однаковим user_id.
Правильний варіант:
CREATE TABLE profiles (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL UNIQUE,
first_name VARCHAR(50) NOT NULL,
FOREIGN KEY (user_id)
REFERENCES users (id)
);Або можна створити окреме обмеження:
CREATE TABLE profiles (
id BIGSERIAL PRIMARY KEY,
user_id BIGINT NOT NULL,
first_name VARCHAR(50) NOT NULL,
CONSTRAINT profiles_user_id_unique UNIQUE (user_id),
CONSTRAINT profiles_user_id_fkey
FOREIGN KEY (user_id)
REFERENCES users (id)
);INSERT INTO profiles (user_id, first_name, last_name)
VALUES (999, 'Ім’я', 'Прізвище');Якщо користувача з id = 999 немає, PostgreSQL відхилить вставку через зовнішній ключ.
NOT NULL не враховує необов’язковий профільNOT NULL на profiles.user_id означає, що кожен профіль обов’язково повинен належати користувачу. Це правильно для таблиці профілів.
Але це не означає, що кожен користувач повинен мати профіль. Користувач без профілю просто не матиме відповідного рядка в таблиці profiles.
INNER JOIN, коли потрібні всі користувачіЯкщо використати INNER JOIN, користувачі без профілю зникнуть із результату. Для отримання всіх користувачів потрібно використовувати LEFT JOIN.
Необов’язково зберігати всі поля профілю в таблиці users. Розділення таблиць може бути зручним, якщо:
профіль має багато додаткових полів;
профіль створюється окремо від користувача;
доступ до даних профілю потрібно організувати окремо.
Головне — забезпечити зв’язок через зовнішній ключ і UNIQUE.
Зв’язок One-to-One означає, що одному запису відповідає не більше одного пов’язаного запису.
Для його створення використовуйте зовнішній ключ із обмеженням UNIQUE.
FOREIGN KEY перевіряє існування пов’язаного запису.
UNIQUE не дозволяє створити другий пов’язаний запис для того самого запису.
INNER JOIN повертає лише записи з відповідними профілями.
LEFT JOIN повертає всіх користувачів, навіть якщо профіль відсутній.
ON DELETE CASCADE може автоматично видаляти профіль разом із користувачем.