Связи: 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

Вот там связанные таблицы начинают выглядеть мощно.

И намного читабельнее.