Пошук уроків, статей та іншого контенту
Розберемо таблиці, рядки, стовпці, ключі та зв’язки, на яких ґрунтується реляційне зберігання даних.
Реляційна модель даних зберігає інформацію у вигляді пов’язаних таблиць. Саме на цій моделі ґрунтується PostgreSQL.
Таблиця складається з:
стовпців — описують властивості об’єктів;
рядків — містять дані про окремі об’єкти;
ключів — ідентифікують записи та встановлюють зв’язки між таблицями.
Наприклад, інформацію про користувачів можна подати так:
| id | name | email | |---:|---|---| | 1 | Олена | olena@example.com | | 2 | Андрій | andrii@example.com |
У цьому прикладі:
users — назва таблиці;
id, name, email — стовпці;
кожен горизонтальний запис — рядок;
id — унікальний ідентифікатор користувача.
Таблиця зазвичай описує один тип сутностей предметної області:
users — користувачів;
products — товари;
orders — замовлення;
comments — коментарі.
Кожен стовпець має назву та тип даних. Наприклад:
CREATE TABLE users (
id integer,
name text,
email text
);Тут:
id має тип integer — ціле число;
name має тип text — текст;
email має тип text.
Структура таблиці визначає, які дані в ній можна зберігати.
Рядок — це один запис у таблиці. Додати рядки можна за допомогою INSERT:
INSERT INTO users (id, name, email)
VALUES
(1, 'Олена', 'olena@example.com'),
(2, 'Андрій', 'andrii@example.com');Переглянути всі рядки можна за допомогою SELECT:
SELECT id, name, email
FROM users;Результат міститиме два записи — по одному для кожного користувача.
Стовпець має:
назву;
тип даних;
за потреби — додаткові обмеження.
Наприклад, можна заборонити порожнє значення в імені:
CREATE TABLE users (
id integer,
name text NOT NULL,
email text
);Обмеження NOT NULL означає, що для кожного рядка стовпець name повинен містити значення.
Первинний ключ (PRIMARY KEY) однозначно ідентифікує кожен рядок таблиці.
CREATE TABLE users (
id integer PRIMARY KEY,
name text NOT NULL,
email text NOT NULL
);Первинний ключ має такі властивості:
значення має бути унікальним;
значення не може бути NULL;
у таблиці може бути один первинний ключ.
Наприклад, два користувачі не можуть мати однаковий id:
INSERT INTO users (id, name, email)
VALUES (1, 'Олена', 'olena@example.com');
-- Такий запис спричинить помилку,
-- оскільки значення id = 1 вже існує.
INSERT INTO users (id, name, email)
VALUES (1, 'Андрій', 'andrii@example.com');Найчастіше первинним ключем є числовий ідентифікатор. У PostgreSQL для автоматичного створення таких значень можна використати GENERATED ... AS IDENTITY:
CREATE TABLE users (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
email text NOT NULL
);Тепер id створюється автоматично:
INSERT INTO users (name, email)
VALUES
('Олена', 'olena@example.com'),
('Андрій', 'andrii@example.com');Зовнішній ключ (FOREIGN KEY) посилається на первинний ключ іншої таблиці. Він створює зв’язок між таблицями.
Нехай кожне замовлення належить одному користувачеві. Тоді створимо таблицю замовлень:
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id integer NOT NULL,
total numeric(10, 2) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);Стовпець user_id зберігає ідентифікатор користувача, якому належить замовлення.
Обмеження
FOREIGN KEY (user_id) REFERENCES users(id)означає, що значення user_id повинно існувати в users.id.
Тому таке замовлення додати не можна, якщо користувача з id = 999 немає:
INSERT INTO orders (user_id, total)
VALUES (999, 150.00);Зовнішні ключі захищають цілісність даних. Вони не дозволяють таблиці містити посилання на неіснуючі записи.
Зв’язок один до одного означає, що одному запису першої таблиці відповідає не більше одного запису другої таблиці.
Наприклад:
користувач має один профіль;
профіль належить одному користувачеві.
Для такого зв’язку зовнішній ключ також має бути унікальним:
CREATE TABLE user_profiles (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id integer UNIQUE NOT NULL,
bio text,
FOREIGN KEY (user_id) REFERENCES users(id)
);Обмеження UNIQUE не дозволяє створити два профілі для одного користувача.
Зв’язок один до багатьох означає, що одному запису першої таблиці можуть відповідати багато записів другої.
Наприклад:
один користувач може мати багато замовлень;
кожне замовлення належить одному користувачеві.
У цьому випадку зовнішній ключ розміщується в таблиці, яка містить багато записів:
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id integer NOT NULL,
total numeric(10, 2) NOT NULL,
FOREIGN KEY (user_id) REFERENCES users(id)
);У orders може бути багато рядків з однаковим user_id, але кожен такий ідентифікатор повинен відповідати існуючому користувачеві.
Зв’язок багато до багатьох означає, що:
один запис першої таблиці може бути пов’язаний з багатьма записами другої;
один запис другої таблиці може бути пов’язаний з багатьма записами першої.
Наприклад:
один студент може відвідувати багато курсів;
один курс можуть відвідувати багато студентів.
Для такого зв’язку створюють проміжну таблицю:
CREATE TABLE students (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
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),
FOREIGN KEY (course_id) REFERENCES courses(id)
);Первинний ключ
PRIMARY KEY (student_id, course_id)складається з двох стовпців. Такий ключ називають складеним. Він не дозволяє двічі додати одну й ту саму пару «студент — курс».
Щоб отримати дані з кількох пов’язаних таблиць, використовують JOIN.
Повний приклад:
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS users;
CREATE TABLE users (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name text NOT NULL,
email text NOT NULL UNIQUE
);
CREATE TABLE orders (
id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id integer NOT NULL,
total numeric(10, 2) NOT NULL CHECK (total >= 0),
FOREIGN KEY (user_id) REFERENCES users(id)
);
INSERT INTO users (name, email)
VALUES
('Олена', 'olena@example.com'),
('Андрій', 'andrii@example.com');
INSERT INTO orders (user_id, total)
VALUES
(1, 1250.50),
(1, 300.00),
(2, 890.00);
-- Отримуємо замовлення разом з іменами користувачів.
SELECT
users.name,
orders.id AS order_id,
orders.total
FROM users
JOIN orders ON orders.user_id = users.id
ORDER BY orders.id;Запит поєднує таблиці за умовою:
orders.user_id = users.idРезультат матиме приблизно такий вигляд:
name | order_id | total
--------+----------+---------
Олена | 1 | 1250.50
Олена | 2 | 300.00
Андрій | 3 | 890.00Одне ім’я Олена повторюється, оскільки в неї є два замовлення. Це нормально: результат JOIN описує знайдені поєднання рядків, а не змінює самі таблиці.
Обмеження допомагають базі даних не зберігати некоректні значення.
NOT NULLЗабороняє пропущене значення:
name text NOT NULLUNIQUEЗабороняє дублікати:
email text UNIQUEНаприклад, двом користувачам не можна призначити одну й ту саму електронну адресу.
CHECKПеревіряє умову для значення:
total numeric(10, 2) CHECK (total >= 0)Таке обмеження не дозволяє зберегти від’ємну суму замовлення.
PRIMARY KEYОднозначно ідентифікує рядок і не допускає повторів або NULL.
FOREIGN KEYПеревіряє, що посилання на іншу таблицю є коректним.
У реляційній моделі кожен факт бажано зберігати в одному відповідному місці.
Не варто записувати ім’я користувача безпосередньо в кожному замовленні:
orders
------------------------------------------------
id | user_name | total
1 | Олена | 1250.50
2 | Олена | 300.00Якщо ім’я зміниться, доведеться оновлювати багато рядків. Крім того, записи можуть містити різні варіанти одного імені.
Краще розділити дані:
users
-------------------------
id | name
1 | Олена
orders
-------------------------
id | user_id | total
1 | 1 | 1250.50
2 | 1 | 300.00Тепер ім’я зберігається в users, а orders містить лише посилання на користувача. Це зменшує дублювання та допомагає підтримувати узгодженість даних.
Таблиця без первинного ключа може містити дублікати, які складно відрізнити один від одного.
Для більшості таблиць варто визначати стовпець або набір стовпців як PRIMARY KEY.
Не слід зберігати список товарів одним текстовим рядком:
"книга, ручка, зошит"Такі дані складно фільтрувати, змінювати та пов’язувати з іншими таблицями. Для окремих товарів потрібні окремі рядки, а для складніших зв’язків — окрема проміжна таблиця.
Не варто зберігати в замовленні ім’я користувача замість user_id. Імена можуть повторюватися або змінюватися.
Краще використовувати стабільний ідентифікатор:
user_id integer REFERENCES users(id)NOT NULLЯкщо значення є обов’язковим для кожного запису, це потрібно явно вказати за допомогою NOT NULL. Інакше база даних дозволить створити рядок без цього значення.
Первинний ключ ідентифікує рядок у власній таблиці.
Зовнішній ключ посилається на рядок в іншій таблиці.
Наприклад, у таблиці orders:
orders.id — первинний ключ;
orders.user_id — зовнішній ключ.
Реляційна база даних зберігає дані у таблицях.
Таблиця містить стовпці та рядки.
Стовпець має назву, тип даних і може мати обмеження.
Первинний ключ однозначно ідентифікує рядок.
Зовнішній ключ створює зв’язок між таблицями.
Основні типи зв’язків — один до одного, один до багатьох і багато до багатьох.
Для зв’язку багато до багатьох використовують проміжну таблицю.
NOT NULL, UNIQUE, CHECK, PRIMARY KEY і FOREIGN KEY допомагають підтримувати цілісність даних.
Розділення сутностей по таблицях зменшує дублювання та спрощує оновлення даних.