Звʼязки: Primary Keys і Foreign Keys

Вітаю знову.

У попередній лекції ти навчився фільтрувати й сортувати дані.

Ти використовував:

• WHERE;
• оператори порівняння;
• AND;
• OR;
• LIKE;
• ILIKE;
• ORDER BY;
• LIMIT;
• OFFSET.

Дуже корисно.

Дуже SQL.

Сьогодні ми переходимо до однієї з найважливіших ідей у реляційних базах даних:

Звʼязки.

PostgreSQL — це реляційна база даних.

Це означає, що таблиці можуть бути повʼязані між собою.

Таблиця не повинна зберігати все сама, як самотній Excel-файл у темній папці.

Замість цього ми можемо розділити дані на чисті таблиці й зʼєднати їх за допомогою ключів.

Ось тут PostgreSQL стає серйозним.

Все ще дружнім.

Але серйозним.

Що Ти Вивчиш

У цій лекції ти вивчиш:

• що таке primary key;
• що таке foreign key;
• чому звʼязки важливі;
• як таблиці можуть посилатися на інші таблиці;
• що таке звʼязок one-to-many;
• як створювати повʼязані таблиці;
• як вставляти повʼязані дані;
• що відбувається, коли foreign key вказує на неіснуючі дані;
• чому дубльовані дані небезпечні;
• як PostgreSQL захищає звʼязки;
• як підготуватися до JOIN.

Наприкінці цієї лекції ти зрозумієш, як таблиці можуть працювати разом.

Це великий крок.

Одна таблиця корисна.

Повʼязані таблиці — потужні.

Як інструменти в майстерні.

Одна викрутка — добре.

Повний ящик інструментів — краще.

Якщо він не впаде тобі на ногу.

Проблема Однієї Великої Таблиці

Уяви, що ми хочемо зберігати товари й категорії.

Ми могли б створити одну таблицю так:

id | product_name | category_name
---|--------------|---------------
1  | Laptop       | Electronics
2  | Mouse        | Electronics
3  | Desk Chair   | Furniture
4  | Bookshelf    | Furniture

Це працює.

Спочатку.

Але тут є дублювання.

Слово Electronics повторюється знову і знову.

Слово Furniture теж повторюється.

А тепер уяви 10 000 товарів.

Якщо треба перейменувати Electronics на Electronic Devices, тобі доведеться оновлювати багато рядків.

Це дратує.

І небезпечно.

Бо один рядок може стати:

Electronic Devices

Інший може залишитися:

Electronics

А ще один може стати:

Eletronics

Вітаю.

Тепер у тебе три категорії.

І одна орфографічна травма.

Краща Структура

Замість того щоб багато разів зберігати назви категорій, ми можемо створити дві таблиці:

categories
products

Таблиця categories зберігає категорії один раз.

Таблиця products зберігає товари й посилається на категорію.

Приклад:

categories

id | name
---|-------------
1  | Electronics
2  | Furniture

products

id | name       | category_id
---|------------|------------
1  | Laptop     | 1
2  | Mouse      | 1
3  | Desk Chair | 2
4  | Bookshelf  | 2

Тепер товари не зберігають назву категорії напряму.

Вони зберігають category_id.

Це зʼєднує кожен товар з категорією.

Чистіше.

Безпечніше.

Менше повторень.

Менше хаосу.

База даних дихає краще.

Мабуть.

Primary Key

Primary key унікально ідентифікує кожен рядок у таблиці.

Приклад:

id SERIAL PRIMARY KEY

У цій таблиці:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Колонка id є primary key.

Це означає, що кожна категорія має унікальний ID.

Приклад:

id | name
---|-------------
1  | Electronics
2  | Furniture

Primary key допомагає PostgreSQL чітко ідентифікувати кожен рядок.

Без плутанини.

Без питання “який саме Electronics ти маєш на увазі?”

Просто:

category id 1

Дуже прямо.

Дуже базоданно.

Foreign Key

Foreign key — це колонка, яка вказує на primary key в іншій таблиці.

Приклад:

category_id INTEGER REFERENCES categories(id)

Це означає:

category_id має посилатися на існуючий id у таблиці categories.

Приклад:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  category_id INTEGER REFERENCES categories(id)
);

Тут:

• products.id — primary key таблиці products;
• products.category_id — foreign key;
• він вказує на categories.id.

Це створює звʼязок.

PostgreSQL тепер знає:

Кожен товар може належати до категорії.

Дуже корисно.

Таблиці починають говорити між собою.

Ввічливо.

Поки що.

Звʼязок One-to-Many

Звʼязок one-to-many означає:

Один рядок в одній таблиці може бути повʼязаний з багатьма рядками в іншій таблиці.

Приклад:

Одна категорія може мати багато товарів.

Electronics може мати:

• Laptop;
• Mouse;
• Keyboard;
• Monitor;
• USB Cable.

Furniture може мати:

• Desk Chair;
• Bookshelf;
• Desk Lamp.

Отже:

categories -> products
одна категорія -> багато товарів

Це один із найпоширеніших звʼязків у базах даних.

Інші приклади:

one customer -> many orders
one author   -> many books
one teacher  -> many courses
one user     -> many posts

Якщо ти будуєш реальні застосунки, ти бачитимеш one-to-many звʼязки всюди.

Вони як гвинти в меблях.

Маленькі.

Важливі.

Часто ігноруються, поки щось не зламається.

Підготуй Базу Даних

Відкрий PostgreSQL:

sudo -iu postgres psql

Підключись до бази даних:

\c learning_postgresql

Якщо цієї бази немає, створи її:

CREATE DATABASE learning_postgresql;

Потім підключись:

\c learning_postgresql

Тепер видали старі таблиці, якщо вони існують.

Важливо:

Спочатку видаляй products, бо вона буде залежати від categories.

DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS categories;

Порядок має значення.

Якщо одна таблиця залежить від іншої, PostgreSQL може не дозволити видалити parent table першою.

PostgreSQL захищає звʼязки.

Як суворий батько.

Але для даних.

Створи Таблицю Categories

Створи таблицю categories:

CREATE TABLE categories (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) UNIQUE NOT NULL
);

Ця таблиця має:

• id;
• name.

id — це primary key.

name обовʼязковий і унікальний.

Це означає, що ми не можемо мати дві категорії з однаковою назвою.

Добре.

Жодного дубльованого Electronics.

Жодної вечірки з Electronics, electronics і Electronicss.

Ну добре, PostgreSQL все ще може вважати різне написання різними значеннями.

Але UNIQUE захищає від точних дублікатів.

База даних сильна.

Не магічна.

Встав Категорії

Встав категорії:

INSERT INTO categories (name)
VALUES
  ('Electronics'),
  ('Furniture'),
  ('Office');

Перевір таблицю:

SELECT * FROM categories;

Ти маєш побачити:

1 | Electronics
2 | Furniture
3 | Office

ID можуть бути іншими, якщо ти вже вставляв дані раніше.

Це нормально.

Не панікуй.

PostgreSQL рахує.

Іноді він памʼятає старі номери навіть після видалення.

Бази даних памʼятають більше, ніж люди очікують.

Трохи страшно.

Створи Таблицю Products

Тепер створи таблицю products:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  price NUMERIC(10, 2) CHECK (price >= 0),
  category_id INTEGER REFERENCES categories(id)
);

Ця таблиця має:

• id;
• name;
• price;
• category_id.

Найважливіша частина:

category_id INTEGER REFERENCES categories(id)

Це створює foreign key.

Це означає, що кожен category_id у products має відповідати існуючому id у categories.

PostgreSQL буде захищати це правило.

Бо PostgreSQL тут не для вигаданих категорій.

Встав Products з Category IDs

Встав товари:

INSERT INTO products (name, price, category_id)
VALUES
  ('Laptop', 900.00, 1),
  ('Mouse', 25.00, 1),
  ('Desk Chair', 150.00, 2),
  ('Notebook', 5.00, 3),
  ('Pen', 2.00, 3);

Тепер перевір:

SELECT * FROM products;

Ти маєш побачити товари з category_id.

Приклад:

id | name       | price  | category_id
---|------------|--------|------------
1  | Laptop     | 900.00 | 1
2  | Mouse      | 25.00  | 1
3  | Desk Chair | 150.00 | 2
4  | Notebook   | 5.00   | 3
5  | Pen        | 2.00   | 3

Це добре.

Але ще не дуже читабельно.

Ми бачимо category_id.

Ми не бачимо назви категорій.

Скоро ми вивчимо JOIN.

А поки зрозумій сам звʼязок.

Товар знає, до якої категорії належить.

Через ID.

Дуже реляційно.

Дуже PostgreSQL.

Спробуй Неправильний Foreign Key

Тепер спробуй вставити товар з категорією, якої не існує:

INSERT INTO products (name, price, category_id)
VALUES ('Mystery Device', 99.00, 999);

PostgreSQL має це відхилити.

Чому?

Бо немає категорії з id = 999.

Це foreign key виконує свою роботу.

Без foreign key PostgreSQL прийняв би цей рядок.

Потім у тебе був би товар, який вказує в нікуди.

Категорія-привид.

Сумний маленький осиротілий рядок.

Foreign keys запобігають цьому.

Вони захищають звʼязки.

Вони кажуть:

Ти не можеш вказувати на те, чого не існує.

Дуже суворо.

Дуже корисно.

Чому Foreign Keys Важливі

Foreign keys захищають твої дані.

Вони запобігають таким ситуаціям:

• товари з відсутніми категоріями;
• orders з відсутніми customers;
• comments з відсутніми posts;
• payments з відсутніми invoices;
• enrollments з відсутніми students.

Без foreign keys база даних може стати неконсистентною.

Приклад:

Product каже category_id = 999
А category 999 не існує

Це погано.

Застосунок може зламатися.

Звіт може бути неправильним.

Developer може почати пити забагато кави.

Foreign keys зменшують цей тип болю.

Не весь біль.

Але частину.

І це важливо.

Parent і Child Tables

У звʼязках часто кажуть:

parent table
child table

У нашому прикладі:

categories = parent table
products   = child table

Чому?

Бо products залежить від categories.

Товар може посилатися на категорію.

Отже:

categories.id

це parent key.

А:

products.category_id

це foreign key.

Інший приклад:

customers = parent table
orders    = child table

Один customer може мати багато orders.

Кожен order посилається на одного customer.

Ця термінологія часто зустрічається.

Не бійся її.

Parent table.

Child table.

Базоданна сімʼя.

Менш емоційна, ніж реальна сімʼя.

Зазвичай.

Перевір Таблиці

Використай:

\d categories

Потім:

\d products

У таблиці products PostgreSQL має показати foreign key constraint.

Ти можеш побачити щось типу:

Foreign-key constraints:
  "products_category_id_fkey" FOREIGN KEY (category_id) REFERENCES categories(id)

Назва може бути згенерована автоматично.

Вона може виглядати негарно.

Це нормально.

PostgreSQL називає constraints так, ніби робот називає дітей.

Функціонально.

Не поетично.

Проблема з DELETE: Parent Row Використовується

Спробуй видалити категорію, яка має товари:

DELETE FROM categories
WHERE id = 1;

PostgreSQL може це відхилити.

Чому?

Бо товари все ще посилаються на категорію 1.

Якби PostgreSQL дозволив видалення, товари вказували б на відсутню категорію.

Знову осиротілі рядки.

PostgreSQL каже:

Ні. Спочатку розберися з дітьми.

Дуже батьківсько.

Дуже базоданно.

Щоб видалити категорію, спочатку треба оновити або видалити повʼязані товари.

Приклад:

DELETE FROM products
WHERE category_id = 1;

Потім:

DELETE FROM categories
WHERE id = 1;

Будь обережний з delete.

Завжди використовуй SELECT перед видаленням.

Ти знаєш правило.

Шолом на голову.

Поведінка ON DELETE

Foreign keys можуть визначати, що відбувається, коли parent row видаляється.

Популярні варіанти:

• ON DELETE RESTRICT;
• ON DELETE CASCADE;
• ON DELETE SET NULL.

За замовчуванням PostgreSQL зазвичай не дозволяє видаляти parent rows, які ще використовуються.

Це безпечно.

ON DELETE CASCADE означає:

Якщо parent видаляється, видали також повʼязані child rows.

Приклад:

category_id INTEGER REFERENCES categories(id) ON DELETE CASCADE

Будь обережний.

CASCADE потужний.

Іноді корисний.

Іноді небезпечний.

Він може автоматично видалити багато повʼязаних рядків.

Як доміно.

Але з даними.

У beginner-проєктах використовуй це тільки тоді, коли справді розумієш наслідки.

База даних не скаже:

Ти емоційно впевнений?

Вона просто зробить.

Уникай Повторення Даних

Звʼязки допомагають уникати дублювання даних.

Поганий дизайн:

products

id | name   | category_name
---|--------|--------------
1  | Laptop | Electronics
2  | Mouse  | Electronics

Кращий дизайн:

categories

id | name
---|-------------
1  | Electronics

products

id | name   | category_id
---|--------|------------
1  | Laptop | 1
2  | Mouse  | 1

Тепер назва категорії існує в одному місці.

Чистіше.

Якщо назва категорії зміниться, ти оновиш один рядок.

Не 500 рядків.

Це одна з причин, чому існують реляційні бази даних.

Менше дублювання.

Краща консистентність.

Менше орфографічних катастроф.

Інший Приклад: Students і Courses

Уяви навчальну платформу.

Один course може мати багато students.

Один student може також записатися на багато courses.

Це many-to-many relationship.

Many-to-many звʼязки потребують додаткової таблиці.

Приклад:

students
courses
enrollments

Таблиця enrollments зʼєднує students і courses.

Ми глибше вивчимо це пізніше.

Поки запамʼятай:

• one-to-many використовує один foreign key;
• many-to-many зазвичай використовує таблицю-зʼєднання.

Не панікуй.

Це нормально.

Бази даних люблять структуру.

Іноді багато структури.

Як дуже організована бібліотекарка.

Простий One-to-Many Приклад: Authors і Books

Створімо ще один простий звʼязок.

Один author може мати багато books.

Спочатку видали старі таблиці, якщо потрібно:

DROP TABLE IF EXISTS books;
DROP TABLE IF EXISTS authors;

Створи authors:

CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

Створи books:

CREATE TABLE books (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  author_id INTEGER REFERENCES authors(id)
);

Встав authors:

INSERT INTO authors (name)
VALUES
  ('George Orwell'),
  ('Jane Austen');

Встав books:

INSERT INTO books (title, author_id)
VALUES
  ('1984', 1),
  ('Animal Farm', 1),
  ('Pride and Prejudice', 2);

Перевір:

SELECT * FROM authors;
SELECT * FROM books;

Тепер один author може мати багато books.

Це one-to-many.

Просто.

Потужно.

Класично.

Як чай.

Але SQL.

Типові Помилки

Створити Child Table Першою

Неправильний порядок:

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  category_id INTEGER REFERENCES categories(id)
);

Якщо categories ще не існує, PostgreSQL не може створити foreign key.

Спочатку створи parent table.

Потім child table.

Правильний порядок:

1. categories
2. products

Спочатку батьки.

Потім діти.

Планування базоданної сімʼї.

Вставляти Child Rows Перед Parent Rows

Неправильно:

INSERT INTO products (name, price, category_id)
VALUES ('Laptop', 900.00, 1);

Якщо категорія 1 не існує, PostgreSQL це відхилить.

Правильно:

1. Встав categories
2. Встав products

Рядок, на який ти посилаєшся, має існувати першим.

Не можна вказувати на стілець, якого немає.

Хіба що ти займаєшся філософією.

Це SQL.

Використовувати Назви Замість ID

Погана ідея:

product зберігає category_name напряму

Краще:

product зберігає category_id

Назви можуть змінюватися.

ID мають залишатися стабільними.

Назва категорії може змінитися з Office на Office Supplies.

Але її ID може залишитися тим самим.

ID нудні.

Саме тому вони корисні.

Ігнорувати Foreign Keys

Можна створювати таблиці без foreign keys.

Але тоді PostgreSQL не може захищати звʼязки.

Це означає, що застосунок має робити всю роботу.

А застосунки пишуть люди.

Люди забувають речі.

Foreign keys — це ремені безпеки бази даних.

Використовуй їх.

Практика

Створи дві таблиці:

customers
orders

Один customer може мати багато orders.

Створи customers:

CREATE TABLE customers (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(150) UNIQUE NOT NULL
);

Створи orders:

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  order_date DATE DEFAULT CURRENT_DATE,
  total NUMERIC(10, 2) CHECK (total >= 0),
  customer_id INTEGER REFERENCES customers(id)
);

Встав customers:

INSERT INTO customers (name, email)
VALUES
  ('Anna', 'anna@example.com'),
  ('Marco', 'marco@example.com');

Встав orders:

INSERT INTO orders (total, customer_id)
VALUES
  (49.99, 1),
  (120.00, 1),
  (35.50, 2);

Перевір:

SELECT * FROM customers;
SELECT * FROM orders;

Потім спробуй вставити order з неіснуючим customer:

INSERT INTO orders (total, customer_id)
VALUES (99.00, 999);

PostgreSQL має це відхилити.

Добре.

Це foreign key захищає твої дані.

Мінічелендж

Створи маленьку структуру бази даних для blog posts.

Тобі потрібні дві таблиці:

authors
posts

Правила:

• один author може мати багато posts;
• кожен post належить одному author;
• імʼя author обовʼязкове;
• title post обовʼязковий;
• content post може бути довгим текстом;
• published status за замовчуванням false.

Рекомендована структура:

CREATE TABLE authors (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  title VARCHAR(150) NOT NULL,
  content TEXT,
  published BOOLEAN DEFAULT false,
  author_id INTEGER REFERENCES authors(id)
);

Встав щонайменше двох authors.

Встав щонайменше три posts.

Потім виконай:

SELECT * FROM authors;
SELECT * FROM posts;

Спробуй вставити post з author_id = 999.

PostgreSQL має це відхилити.

Це не PostgreSQL дратує тебе.

Це PostgreSQL охороняє твої дані, як серйозна бібліотекарка з клавіатурою.

Підсумок

Сьогодні ти вивчив:

• primary key унікально ідентифікує рядок;
• foreign key вказує на primary key в іншій таблиці;
• звʼязки зʼєднують таблиці;
• one-to-many означає, що один рядок може бути повʼязаний з багатьма рядками;
• parent tables використовуються child tables;
• child tables часто містять foreign keys;
• foreign keys запобігають неправильним посиланням;
• PostgreSQL може відхилити рядки, які вказують на неіснуючі дані;
• звʼязки зменшують дублювання даних;
• порядок створення таблиць має значення;
• порядок вставлення даних має значення;
• ON DELETE контролює, що відбувається після видалення parent rows.

Це величезний крок.

Тепер ти переходиш від ізольованих таблиць до дизайну реляційної бази даних.

Саме для цього існує PostgreSQL.

Таблиці — не самотні острови.

Вони можуть зʼєднуватися.

Вони можуть посилатися одна на одну.

Вони можуть захищати одна одну.

Дуже красиво.

У базоданному сенсі.

Наступна Лекція

У наступній лекції ми вивчимо JOIN.

Саме там звʼязки стають видимими в результатах запиту.

Зараз products показують category_id.

Корисно, але не дуже дружньо.

З JOIN ми покажемо:

Laptop | Electronics
Mouse  | Electronics
Chair  | Furniture

Ось там повʼязані таблиці починають виглядати потужно.

І набагато читабельніше.