Урок 4. Связи между таблицами в PostgreSQL: Ключи к эффективной структуре данных

Изучите типы связей между таблицами в PostgreSQL, научитесь работать с первичными и внешними ключами, и освойте JOIN операции для эффективной работы с данными.

Понятие первичного и внешнего ключа

Представьте, что вы создаете базу данных для библиотеки. У вас есть книги и авторы. Каждая книга написана автором, а автор может написать несколько книг. Как связать эту информацию?

Первичный ключ (Primary Key):

• Уникальный идентификатор для каждой записи в таблице
• Обычно это поле id
• Гарантирует уникальность каждой записи

Пример создания таблицы с первичным ключом:

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

Внешний ключ (Foreign Key):

• Ссылается на первичный ключ другой таблицы
• Устанавливает связь между таблицами
• Обеспечивает целостность данных

Пример создания таблицы с внешним ключом:

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

Типы связей: один-к-одному, один-ко-многим, многие-ко-многим

1. Один-к-одному (One-to-One):

• Каждая запись в одной таблице соответствует только одной записи в другой таблице
• Пример: паспорт и человек

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

CREATE TABLE passports (
    id SERIAL PRIMARY KEY,
    passport_number VARCHAR(20) NOT NULL,
    person_id INTEGER UNIQUE REFERENCES persons(id)
);

2. Один-ко-многим (One-to-Many):

• Одна запись в первой таблице может быть связана с несколькими записями во второй таблице
• Пример: автор и книги

Мы уже создали эти таблицы выше: authors и books

3. Многие-ко-многим (Many-to-Many):

• Несколько записей в первой таблице могут быть связаны с несколькими записями во второй таблице
• Требует промежуточной таблицы
• Пример: студенты и курсы

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

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

CREATE TABLE student_courses (
    student_id INTEGER REFERENCES students(id),
    course_id INTEGER REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)
);

JOIN операции: INNER, LEFT, RIGHT, FULL

JOIN операции позволяют нам объединять данные из разных таблиц на основе связей между ними.

1. INNER JOIN:

• Возвращает только те строки, где есть соответствие в обеих таблицах

SELECT books.title, authors.name
FROM books
INNER JOIN authors ON books.author_id = authors.id;

2. LEFT JOIN:

• Возвращает все строки из левой таблицы и соответствующие строки из правой таблицы
• Если соответствия нет, возвращается NULL для столбцов правой таблицы

SELECT authors.name, books.title
FROM authors
LEFT JOIN books ON authors.id = books.author_id;

3. RIGHT JOIN:

• Аналогично LEFT JOIN, но приоритет отдается правой таблице

SELECT authors.name, books.title
FROM books
RIGHT JOIN authors ON books.author_id = authors.id;

4. FULL JOIN:

• Возвращает все строки, когда есть соответствие в одной из таблиц

SELECT authors.name, books.title
FROM authors
FULL JOIN books ON authors.id = books.author_id;

Практический пример:

Давайте создадим базу данных для небольшого онлайн-магазина:

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

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    price DECIMAL(10, 2) NOT NULL,
    category_id INTEGER REFERENCES categories(id)
);

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

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER REFERENCES customers(id),
    order_date DATE NOT NULL
);

CREATE TABLE order_items (
    order_id INTEGER REFERENCES orders(id),
    product_id INTEGER REFERENCES products(id),
    quantity INTEGER NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- Вставим некоторые данные
INSERT INTO categories (name) VALUES ('Electronics'), ('Books'), ('Clothing');
INSERT INTO products (name, price, category_id) VALUES 
    ('Smartphone', 599.99, 1),
    ('Python Cookbook', 39.99, 2),
    ('T-shirt', 19.99, 3);
INSERT INTO customers (name, email) VALUES 
    ('John Doe', '[email protected]'),
    ('Jane Smith', '[email protected]');
INSERT INTO orders (customer_id, order_date) VALUES 
    (1, '2023-06-01'),
    (2, '2023-06-02');
INSERT INTO order_items (order_id, product_id, quantity) VALUES 
    (1, 1, 1),
    (1, 2, 2),
    (2, 3, 3);

-- Теперь давайте выполним запрос, чтобы получить информацию о заказах
SELECT 
    o.id AS order_id,
    c.name AS customer_name,
    p.name AS product_name,
    oi.quantity,
    p.price,
    (oi.quantity * p.price) AS total_price
FROM 
    orders o
    INNER JOIN customers c ON o.customer_id = c.id
    INNER JOIN order_items oi ON o.id = oi.order_id
    INNER JOIN products p ON oi.product_id = p.id;

Этот запрос объединяет информацию из нескольких таблиц, чтобы предоставить полную картину заказов.

В этом уроке мы познакомились с ключевыми концепциями связей между таблицами в PostgreSQL. Мы изучили первичные и внешние ключи, различные типы связей и как использовать JOIN операции для объединения данных из разных таблиц. Эти знания являются фундаментальными для создания эффективных и хорошо структурированных баз данных. В следующем уроке мы углубимся в индексы и оптимизацию запросов, чтобы сделать наши базы данных еще более эффективными.

Домашнее задание:

1. Создайте базу данных для библиотеки с таблицами для книг, авторов и читателей.
2. Установите правильные связи между таблицами.
3. Вставьте некоторые тестовые данные.
4. Напишите запрос, который выводит список всех книг с именами их авторов.
5. Напишите запрос, который показывает всех читателей и книги, которые они взяли (если взяли).

Удачи в изучении PostgreSQL!

Чтобы освоить продвинутые запросы, ознакомьтесь с уроком 5: Мастерство продвинутых запросов в PostgreSQL.