Как создаются отношения между таблицами в реляционной БД?

Отношения между таблицами в реляционных базах данных устанавливаются через систему ключей: первичных ключей (PRIMARY KEY) и внешних ключей (FOREIGN KEY). Эти механизмы обеспечивают целостность данных и реализуют реляционную модель.

Основные компоненты связей:

1. Первичный ключ (PRIMARY KEY) Уникальный идентификатор каждой записи в таблице:

CREATE TABLE departments (
    department_id INT PRIMARY KEY,           -- Простой первичный ключ
    department_name VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Составной первичный ключ (из нескольких столбцов)
CREATE TABLE course_enrollments (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    PRIMARY KEY (student_id, course_id)      -- Составной ключ
);

2. Внешний ключ (FOREIGN KEY) Столбец в одной таблице, который ссылается на PRIMARY KEY другой таблицы:

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    department_id INT,                       -- Столбец для связи

    -- Определение внешнего ключа
    FOREIGN KEY (department_id) 
    REFERENCES departments(department_id)
    ON DELETE SET NULL                       -- Действие при удалении
    ON UPDATE CASCADE                        -- Действие при обновлении
);

Типы отношений между таблицами:

1. Один-ко-многим (1:N) — самый распространенный

-- Таблица «один»
CREATE TABLE authors (
    author_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Таблица «многие»
CREATE TABLE books (
    book_id SERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    author_id INT NOT NULL,

    FOREIGN KEY (author_id) REFERENCES authors(author_id)
    ON DELETE CASCADE  -- При удалении автора удаляются все его книги
);

-- Один автор (J.K. Rowling) → Много книг (Гарри Поттер 1, 2, 3...)

2. Многие-ко-многим (M:N) — через связующую таблицу

-- Исходные таблицы
CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title VARCHAR(200)
);

-- Связующая таблица (junction/association table)
CREATE TABLE student_courses (
    student_id INT,
    course_id INT,
    enrollment_date DATE,
    grade DECIMAL(3,2),

    PRIMARY KEY (student_id, course_id),  -- Составной первичный ключ

    FOREIGN KEY (student_id) 
    REFERENCES students(student_id) 
    ON DELETE CASCADE,

    FOREIGN KEY (course_id) 
    REFERENCES courses(course_id) 
    ON DELETE CASCADE
);

-- Студент John записан на курсы: Math, Physics, Chemistry
-- Курс Math имеет студентов: John, Alice, Bob

3. Один-к-одному (1:1) — реже используется

-- Основная таблица
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL
);

-- Дополнительная информация (разделена для нормализации)
CREATE TABLE user_profiles (
    profile_id SERIAL PRIMARY KEY,
    user_id INT UNIQUE NOT NULL,  -- UNIQUE гарантирует 1:1 отношение
    bio TEXT,
    avatar_url VARCHAR(255),
    date_of_birth DATE,

    FOREIGN KEY (user_id) REFERENCES users(user_id)
    ON DELETE CASCADE
);

-- Каждый пользователь имеет ровно один профиль
-- Каждый профиль принадлежит ровно одному пользователю

Опции внешних ключей (referential actions):

FOREIGN KEY (column) REFERENCES parent_table(parent_column)
    ON DELETE NO ACTION      -- Запретить удаление (по умолчанию)
    ON DELETE RESTRICT       -- Запретить удаление (проверяется сразу)
    ON DELETE CASCADE        -- Удалить дочерние записи
    ON DELETE SET NULL       -- Установить NULL в дочерних записях
    ON DELETE SET DEFAULT    -- Установить значение по умолчанию

    ON UPDATE аналогичные опции

Практический пример полной схемы:

-- Создание связанных таблиц для интернет-магазина

-- 1. Таблицы справочники
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    parent_category_id INT NULL,
    FOREIGN KEY (parent_category_id) REFERENCES categories(category_id)
);

-- 2. Основные таблицы
CREATE TABLE products (
    product_id SERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    description TEXT,
    price DECIMAL(10,2) NOT NULL,
    category_id INT NOT NULL,
    supplier_id INT NOT NULL,

    FOREIGN KEY (category_id) REFERENCES categories(category_id),
    FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id),

    CHECK (price > 0)  -- Дополнительное ограничение
);

CREATE TABLE customers (
    customer_id SERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    full_name VARCHAR(150) NOT NULL
);

-- 3. Таблица заказов (1:N с customers)
CREATE TABLE orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(20) DEFAULT 'pending',

    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled'))
);

-- 4. Связующая таблица для M:N (продукты в заказах)
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL DEFAULT 1,
    unit_price DECIMAL(10,2) NOT NULL,

    PRIMARY KEY (order_id, product_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id) ON DELETE CASCADE,
    FOREIGN KEY (product_id) REFERENCES products(product_id),

    CHECK (quantity > 0),
    CHECK (unit_price > 0)
);

Визуализация связей через запросы:

-- Получение заказов с информацией о клиентах и товарах
SELECT 
    o.order_id,
    o.order_date,
    c.full_name AS customer_name,
    c.email,
    p.name AS product_name,
    oi.quantity,
    oi.unit_price,
    (oi.quantity * oi.unit_price) AS item_total
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
INNER JOIN products p ON oi.product_id = p.product_id
WHERE o.status = 'shipped'
ORDER BY o.order_date DESC;

Best practices для проектирования отношений:

1. Именование ключей:

   • Первичный ключ: [table_name]_id (например, product_id)
   • Внешний ключ: то же имя, что и первичный ключ родительской таблицы

2. Индексы на внешних ключах:

   CREATE INDEX idx_orders_customer_id ON orders(customer_id);
   CREATE INDEX idx_order_items_product_id ON order_items(product_id);

3. Каскадные операции с осторожностью:

   • ON DELETE CASCADE может неожиданно удалить много данных
   • Часто лучше использовать ON DELETE SET NULL или RESTRICT

4. Нормализация:

   • Разделяйте данные на логические таблицы
   • Избегайте избыточности данных
   • Используйте связи для поддержания целостности

5. Денормализация для производительности:

   -- Иногда дублирование данных оправдано
   CREATE TABLE order_summary (
      order_id INT PRIMARY KEY,
      customer_name VARCHAR(150),  -- Дублирование из customers
      total_amount DECIMAL(10,2),  -- Вычисляемое значение
      item_count INT               -- Агрегированные данные
   );

Проверка существующих связей в БД:

-- PostgreSQL
SELECT 
    tc.table_name, 
    kcu.column_name, 
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
    ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage ccu
    ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

-- MySQL
SELECT 
    TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, 
    REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL;