Что такое ForeignKey?

Foreign Key (Внешний ключ): полное руководство

Что такое Foreign Key в двух словах

Foreign Key — это ограничение целостности, которое связывает две таблицы. Оно гарантирует, что значение в одной таблице соответствует значению в другой таблице.

Визуальный пример

Таблица: Users (главная таблица)
┌─────────────┬──────────┐
│ user_id (PK)│ name     │
├─────────────┼──────────┤
│ 1           │ Alice    │
│ 2           │ Bob      │
│ 3           │ Charlie  │
└─────────────┴──────────┘
        ↑ (связь)
        │
Таблица: Orders (зависимая таблица)
┌──────────┬──────────────┬─────────┐
│ order_id │ user_id (FK) │ amount  │
├──────────┼──────────────┼─────────┤
│ 1        │ 1            │ 1000    │ ← указывает на Alice
│ 2        │ 2            │ 2000    │ ← указывает на Bob
│ 3        │ 1            │ 1500    │ ← указывает на Alice
└──────────┴──────────────┴─────────┘

FK гарантирует: order.user_id всегда существует в users.user_id

SQL синтаксис

Создание FK при создании таблицы

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100)
);

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2),
    created_at TIMESTAMP,
    
    -- Foreign Key ограничение
    FOREIGN KEY (user_id) REFERENCES users(user_id)
);

Добавление FK к существующей таблице

ALTER TABLE orders
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(user_id);

Каскадное удаление (CASCADE)

Проблема без CASCADE:

-- Хотим удалить пользователя
DELETE FROM users WHERE user_id = 1;
-- Ошибка! Есть заказы этого пользователя
-- FOREIGN KEY constraint failed

Решение: CASCADE

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2),
    
    FOREIGN KEY (user_id) REFERENCES users(user_id)
    ON DELETE CASCADE  -- удалить заказы при удалении пользователя
);

-- Теперь удаление работает
DELETE FROM users WHERE user_id = 1;
-- Автоматически удалятся все заказы пользователя 1

Варианты ON DELETE

-- 1. CASCADE: удалить зависимые записи
FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE CASCADE;

-- 2. RESTRICT (по умолчанию): запретить удаление
FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE RESTRICT;  -- DELETE не сработает

-- 3. SET NULL: обнулить FK
FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE SET NULL;  -- user_id = NULL

-- 4. SET DEFAULT: установить значение по умолчанию
FOREIGN KEY (user_id) REFERENCES users(user_id)
ON DELETE SET DEFAULT;  -- user_id = некое значение

Реальный пример: интернет-магазин

-- Категории товаров
CREATE TABLE categories (
    category_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

-- Товары (FK к категориям)
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    category_id INT NOT NULL,
    name VARCHAR(200),
    price DECIMAL(10, 2),
    FOREIGN KEY (category_id) REFERENCES categories(category_id)
);

-- Пользователи
CREATE TABLE users (
    user_id INT PRIMARY KEY,
    email VARCHAR(100),
    country_id INT,
    FOREIGN KEY (country_id) REFERENCES countries(country_id)
);

-- Заказы (FK к пользователям)
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT NOT NULL,
    order_date DATE,
    total_amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(user_id)
    ON DELETE CASCADE  -- если удалить юзера, удалить его заказы
);

-- Позиции заказов (FK к заказам и товарам)
CREATE TABLE order_items (
    item_id INT PRIMARY KEY,
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT,
    price DECIMAL(10, 2),
    
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
    ON DELETE CASCADE,  -- если удалить заказ, удалить позиции
    
    FOREIGN KEY (product_id) REFERENCES products(product_id)
    ON DELETE RESTRICT  -- не можем удалить товар если он в заказе
);

Что происходит с FK при нарушении

Попытка вставить несуществующее значение:

-- Попытка создать заказ несуществующего пользователя
INSERT INTO orders (order_id, user_id, amount)
VALUES (100, 999, 1000);

-- Ошибка: foreign key constraint violation
-- ERROR: insert or update on table "orders" violates foreign key constraint

Попытка удалить запись на которую ссылаются:

-- user_id = 1 существует в orders
DELETE FROM users WHERE user_id = 1;

-- Без CASCADE: Error
-- ERROR: update or delete on table "users" violates foreign key constraint

FK vs JOIN в аналитике

FK используется в БД для целостности. Но в анализе мы используем JOIN'ы:

-- Даже если FK не определена, можем делать JOIN
SELECT 
    o.order_id,
    o.amount,
    u.email
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id;

-- FK просто гарантирует что o.user_id всегда будет найдена
-- Без FK LEFT JOIN вернет NULL для user_id которого нет в users

Индексирование FK

Важно: FK нужны индексы для быстрого поиска

-- Некоторые БД создают индекс автоматически
-- Но лучше быть явным
CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    user_id INT NOT NULL,
    amount DECIMAL(10, 2),
    
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    INDEX idx_user_id (user_id)  -- индекс для быстрых JOIN'ов
);

SQLAlchemy ORM: Foreign Keys

Если работаешь в Python:

from sqlalchemy import create_engine, Column, Integer, String, ForeignKey, Numeric
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    user_id = Column(Integer, primary_key=True)
    name = Column(String(100))
    
    # Relationships
    orders = relationship('Order', back_populates='user')

class Order(Base):
    __tablename__ = 'orders'
    
    order_id = Column(Integer, primary_key=True)
    user_id = Column(Integer, ForeignKey('users.user_id'), nullable=False)
    amount = Column(Numeric(10, 2))
    
    # Relationships
    user = relationship('User', back_populates='orders')

# Использование
from sqlalchemy.orm import Session

with Session(engine) as session:
    user = session.query(User).filter_by(user_id=1).first()
    print(user.orders)  # Все заказы пользователя (lazy loaded)

Проблемы и их решения

Проблема 1: Циклические FK

Таблица A → FK → Таблица B
Таблица B → FK → Таблица A

Решение: Используй ON DELETE SET NULL

CREATE TABLE users (
    user_id INT PRIMARY KEY,
    manager_id INT,
    FOREIGN KEY (manager_id) REFERENCES users(user_id)
    ON DELETE SET NULL  -- если менеджер удален, обнуляем
);

Проблема 2: Orphan records (сироты)

Если забыть про CASCADE, остаются заказы без пользователя.

-- Найти orphan'ов
SELECT o.order_id, o.user_id
FROM orders o
LEFT JOIN users u ON o.user_id = u.user_id
WHERE u.user_id IS NULL;  -- пользователь не найден

Проблема 3: Медленные DELETE'ы

CASCADE удаление может быть медленным на больших таблицах.

Решение: Batch delete

-- Вместо одного DELETE на 1M записей
DELETE FROM users WHERE user_id = 1;

-- Делаем batch'ами
DELETE FROM users WHERE user_id = 1 AND created_at < '2020-01-01' LIMIT 100000;

Когда FK не нужна

❌ Не создавай FK если:

• Это снижает производительность критично
• Данные не критичны (логи, события)
• Есть нормализация на приложении (в Python коде)
• Работаешь с data lake где целостность не требуется

✅ Создавай FK если:

• Критична целостность данных (финансы, информация пользователя)
• OLTP база данных (транзакции)
• Нужна помощь БД в контроле консистентности

Отличие от Index

-- INDEX просто ускоряет поиск
CREATE INDEX idx_user_id ON orders(user_id);
-- Не гарантирует что user_id существует в users

-- FOREIGN KEY гарантирует целостность
ALTER TABLE orders
ADD FOREIGN KEY (user_id) REFERENCES users(user_id);

Практический совет для аналитика

В аналитике:

• FK подсказывают как соединять таблицы (JOIN ON)
• Помогают понять схему данных
• Убедись что JOIN'аешь по FK

-- Хорошо: используем FK
SELECT u.name, COUNT(o.order_id) as orders_count
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id  -- FK связь
GROUP BY u.name;

-- Плохо: соединяешь случайные поля
SELECT u.name, o.amount
FROM users u
JOIN orders o ON u.email = o.description;  -- не FK!

Итоговая таблица

Важное: FK — это не просто синтаксис, это наложение правил на БД. Они защищают от ошибок данных, но требуют осторожности при удалении.