Пошук уроків, статей та іншого контенту
Розглянете сутності, первинні та зовнішні ключі й принципи зв’язування таблиць у реляційній базі даних.
У реляційній базі даних інформація зберігається в таблицях. Кожна таблиця описує певну сутність:
users — користувачів;
products — товари;
orders — замовлення.
Рядок таблиці представляє один конкретний об’єкт сутності, а стовпці містять його властивості.
Наприклад, рядок у таблиці users може описувати одного користувача:
id | name
---+------
1 | ОленаЩоб пов’язати записи з різних таблиць, PostgreSQL використовує ключі:
первинний ключ
PRIMARY KEYзовнішній ключ (FOREIGN KEY) зберігає посилання на рядок іншої таблиці.
Первинний ключ — це стовпець або набір стовпців, значення яких однозначно визначають кожен рядок таблиці.
Основні властивості первинного ключа:
значення має бути унікальним;
значення не може бути NULL;
у таблиці може бути лише один первинний ключ;
первинний ключ може складатися з одного або кількох стовпців.
Найчастіше як первинний ключ використовують ідентифікатор id:
CREATE TABLE users (
id integer PRIMARY KEY,
name text NOT NULL
);Тепер PostgreSQL не дозволить:
додати два рядки з однаковим id;
додати рядок без значення id;
створити інший первинний ключ у цій таблиці.
Для автоматичної генерації числових ідентифікаторів у PostgreSQL можна використати тип GENERATED ALWAYS AS IDENTITY:
CREATE TABLE users (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);Під час вставки id можна не вказувати:
INSERT INTO users (name)
VALUES ('Олена');PostgreSQL автоматично створить значення ідентифікатора.
Зовнішній ключ — це стовпець, який посилається на первинний ключ іншої таблиці.
Наприклад, кожне замовлення належить певному користувачеві. У таблиці orders можна створити стовпець user_id, який посилається на users.id:
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id integer NOT NULL REFERENCES users(id),
created_at timestamp NOT NULL DEFAULT now()
);Тут:
orders.id — первинний ключ замовлення;
orders.user_id — зовнішній ключ;
REFERENCES users(id) визначає, на яку таблицю і стовпець посилається ключ.
PostgreSQL не дозволить додати замовлення для користувача, якого не існує:
INSERT INTO orders (user_id)
VALUES (999);Якщо користувача з id = 999 немає, база даних поверне помилку порушення зовнішнього ключа.
Це називається посилальною цілісністю: база даних не допускає посилань на неіснуючі записи.
Зв’язок «один до багатьох» означає:
один запис у першій таблиці може мати багато записів у другій;
кожен запис у другій таблиці належить одному запису в першій.
Приклад:
один користувач може мати багато замовлень;
кожне замовлення належить одному користувачу.
Схема такого зв’язку:
users 1 ──── багато ordersЗовнішній ключ розміщують у таблиці, де зберігається «багато»:
CREATE TABLE users (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id integer NOT NULL REFERENCES users(id),
created_at timestamp NOT NULL DEFAULT now()
);Зв’язок «один до одного» означає, що одному запису першої таблиці відповідає не більш як один запис другої.
Наприклад, користувач і його профіль:
CREATE TABLE users (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL
);
CREATE TABLE profiles (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id integer UNIQUE NOT NULL REFERENCES users(id),
biography text
);Обмеження UNIQUE для profiles.user_id гарантує, що один користувач не матиме двох профілів.
Без UNIQUE база даних дозволила б створити кілька профілів для одного користувача, і зв’язок став би «один до багатьох».
Зв’язок «багато до багатьох» означає, що:
один запис першої таблиці може бути пов’язаний із багатьма записами другої;
один запис другої таблиці також може бути пов’язаний із багатьма записами першої.
Наприклад:
одне замовлення містить багато товарів;
один товар може входити до багатьох замовлень.
Безпосередньо зберегти такий зв’язок в одній таблиці незручно. Для нього створюють проміжну таблицю:
orders 1 ──── багато order_items багато ──── 1 productsПроміжна таблиця order_items містить зовнішні ключі на обидві таблиці:
CREATE TABLE products (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
price numeric(10, 2) NOT NULL
);
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id integer NOT NULL REFERENCES users(id)
);
CREATE TABLE order_items (
order_id integer NOT NULL REFERENCES orders(id),
product_id integer NOT NULL REFERENCES products(id),
quantity integer NOT NULL CHECK (quantity > 0),
PRIMARY KEY (order_id, product_id)
);У order_items використано складений первинний ключ:
PRIMARY KEY (order_id, product_id)Він не дозволяє додати один і той самий товар до одного замовлення двічі. Кількість товару зберігається в quantity.
Нижче наведено приклад із користувачами, замовленнями та товарами. Його можна виконати в PostgreSQL.
-- Видаляємо таблиці в правильному порядку через залежності між ними
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
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,
price numeric(10, 2) NOT NULL CHECK (price >= 0)
);
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id integer NOT NULL REFERENCES users(id),
created_at timestamp NOT NULL DEFAULT now()
);
CREATE TABLE order_items (
order_id integer NOT NULL REFERENCES orders(id),
product_id integer NOT NULL REFERENCES products(id),
quantity integer NOT NULL CHECK (quantity > 0),
PRIMARY KEY (order_id, product_id)
);
INSERT INTO users (name)
VALUES
('Олена'),
('Андрій');
INSERT INTO products (name, price)
VALUES
('Клавіатура', 1500.00),
('Миша', 800.00),
('Монітор', 7000.00);
INSERT INTO orders (user_id)
VALUES
(1),
(1),
(2);
INSERT INTO order_items (order_id, product_id, quantity)
VALUES
(1, 1, 1),
(1, 2, 2),
(2, 3, 1),
(3, 2, 1);
-- Отримуємо замовлення разом з іменами користувачів
SELECT
orders.id AS order_id,
users.name AS customer_name,
orders.created_at
FROM orders
JOIN users ON users.id = orders.user_id
ORDER BY orders.id;
-- Отримуємо склад замовлення
SELECT
orders.id AS order_id,
users.name AS customer_name,
products.name AS product_name,
order_items.quantity,
products.price,
order_items.quantity * products.price AS item_total
FROM orders
JOIN users ON users.id = orders.user_id
JOIN order_items ON order_items.order_id = orders.id
JOIN products ON products.id = order_items.product_id
ORDER BY orders.id, products.name;У цьому прикладі:
users і orders пов’язані зв’язком «один до багатьох»;
orders і products пов’язані зв’язком «багато до багатьох» через order_items;
зовнішні ключі не дозволяють створити замовлення для неіснуючого користувача;
зовнішні ключі не дозволяють додати до замовлення неіснуючий товар;
JOIN об’єднує пов’язані рядки під час читання даних.
Зовнішній ключ зберігає зв’язок, але сам по собі не додає до результату дані з іншої таблиці. Для цього використовують JOIN.
Наприклад, щоб отримати замовлення конкретного користувача:
SELECT
orders.id,
orders.created_at
FROM orders
JOIN users ON users.id = orders.user_id
WHERE users.name = 'Олена';Умова:
users.id = orders.user_idпорівнює первинний ключ користувача із зовнішнім ключем у замовленні.
Щоб показати дані з обох таблиць, можна вибрати стовпці з users і orders:
SELECT
users.name,
orders.id AS order_id
FROM users
JOIN orders ON orders.user_id = users.id;Якщо на рядок посилаються інші таблиці, PostgreSQL за замовчуванням не дозволить його видалити.
Наприклад, якщо користувач має замовлення, така операція може завершитися помилкою:
DELETE FROM users
WHERE id = 1;Це захищає базу від ситуації, коли в orders залишиться зовнішній ключ, що посилається на неіснуючого користувача.
Поведінку під час видалення можна визначити явно. Наприклад:
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id integer NOT NULL REFERENCES users(id) ON DELETE RESTRICT
);ON DELETE RESTRICT забороняє видалення користувача, якщо в нього є замовлення. Це типовий безпечний варіант.
Інший варіант — автоматично видаляти залежні записи:
CREATE TABLE order_items (
order_id integer NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
product_id integer NOT NULL REFERENCES products(id),
quantity integer NOT NULL CHECK (quantity > 0),
PRIMARY KEY (order_id, product_id)
);ON DELETE CASCADE означає: якщо видалити замовлення, PostgreSQL автоматично видалить його позиції з order_items.
Таку поведінку потрібно використовувати обережно, оскільки видалення одного запису може спричинити видалення пов’язаних записів.
Невдалий варіант:
CREATE TABLE orders (
id integer PRIMARY KEY,
product_ids text
);У стовпці product_ids можуть зберігатися значення на кшталт 1,2,3. Такий підхід ускладнює:
перевірку існування товарів;
пошук замовлень із певним товаром;
підрахунок кількості;
підтримку зовнішніх ключів.
Для зв’язку «багато до багатьох» потрібно використовувати проміжну таблицю.
Якщо створити user_id без обмеження REFERENCES, база даних не зможе перевірити, чи існує відповідний користувач:
CREATE TABLE orders (
id integer PRIMARY KEY,
user_id integer NOT NULL
);Краще явно описати зв’язок:
CREATE TABLE orders (
id integer PRIMARY KEY,
user_id integer NOT NULL REFERENCES users(id)
);Зовнішній ключ має посилатися на первинний ключ або інший унікальний стовпець батьківської таблиці:
user_id integer REFERENCES users(id)Назва стовпця зовнішнього ключа може відрізнятися від назви цільового стовпця, але типи даних мають бути сумісними.
Якщо в order_items не визначити первинний або унікальний ключ, можна випадково додати той самий товар до одного замовлення кілька разів:
PRIMARY KEY (order_id, product_id)Складений ключ захищає від такого дублювання.
Таблицю, на яку посилається зовнішній ключ, потрібно створити першою.
Правильний порядок:
users;
products;
orders;
order_items.
Так само під час видалення таблиць спочатку видаляють залежні таблиці, а потім таблиці, на які вони посилаються.
Сутність у реляційній базі даних зазвичай представлена таблицею.
Первинний ключ однозначно ідентифікує рядок.
Зовнішній ключ посилається на запис іншої таблиці.
Зв’язок «один до багатьох» реалізують зовнішнім ключем у таблиці «багато».
Зв’язок «один до одного» додатково потребує UNIQUE для зовнішнього ключа.
Зв’язок «багато до багатьох» реалізують через проміжну таблицю з двома зовнішніми ключами.
Обмеження FOREIGN KEY підтримує посилальну цілісність.
JOIN дає змогу отримувати пов’язані дані з кількох таблиць.
Поведінку під час видалення можна налаштувати за допомогою ON DELETE.