Как работает оператор CASCADE в SQL?

Оператор CASCADE в SQL

CASCADE — это опция в SQL для определения поведения при удалении или обновлении записей, на которые ссылаются другие таблицы через внешние ключи (Foreign Keys).

1. Основная концепция

Когда у нас есть связь "один ко многим" (один пользователь — много заказов), CASCADE определяет что происходит когда мы удаляем родительскую запись:

CREATE TABLE users (
    id UUID PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE
);

CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

Эта схема означает: если удалён пользователь, удалить все его заказы автоматически.

2. Типы CASCADE операций

ON DELETE CASCADE

Удаление родительской записи удаляет все связанные дочерние записи:

-- Удаление пользователя
DELETE FROM users WHERE id = '123e4567-e89b-12d3-a456-426614174000';

-- Все заказы этого пользователя также удаляются
-- (если определено ON DELETE CASCADE)

ON UPDATE CASCADE

Обновление первичного ключа родителя обновляет внешний ключ в детях:

CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id) 
        ON UPDATE CASCADE
        ON DELETE CASCADE
);

-- Если обновим user_id в таблице users
-- все references в orders обновятся автоматически
UPDATE users SET id = 'new-id' WHERE id = 'old-id';
-- ORDER.user_id также изменится на 'new-id'

3. Альтернативные опции (вместо CASCADE)

ON DELETE RESTRICT / NO ACTION

Препятствует удалению, если есть связанные записи:

CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE RESTRICT
);

-- Попытка удалить пользователя с заказами вызовет ошибку:
DELETE FROM users WHERE id = '123';  -- ERROR: constraint violation

ON DELETE SET NULL

Устанавливает внешний ключ в NULL при удалении родителя:

CREATE TABLE comments (
    id UUID PRIMARY KEY,
    post_id UUID,
    text TEXT,
    FOREIGN KEY (post_id) REFERENCES posts(id) ON DELETE SET NULL
);

-- Удаление поста обнуляет post_id в комментариях
DELETE FROM posts WHERE id = '456';
-- SELECT * FROM comments WHERE post_id IS NULL;  -- Содержит старые комментарии

ON DELETE SET DEFAULT

Устанавливает значение по умолчанию:

CREATE TABLE orders (
    id UUID PRIMARY KEY,
    user_id UUID DEFAULT '00000000-0000-0000-0000-000000000000',
    amount DECIMAL(10, 2),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET DEFAULT
);

4. Практический пример в PostgreSQL

-- Таблица пользователей
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(100) UNIQUE NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Таблица заказов
CREATE TABLE orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    product_name VARCHAR(255),
    amount DECIMAL(10, 2),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- Таблица платежей
CREATE TABLE payments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL,
    status VARCHAR(20),
    amount DECIMAL(10, 2),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

-- Каскадное удаление на несколько уровней:
-- Удалим пользователя → удалятся все его заказы → удалятся все платежи

5. SQLAlchemy с CASCADE

В Python ORM SQLAlchemy CASCADE определяется в relationship:

from sqlalchemy import Column, String, ForeignKey, create_engine
from sqlalchemy.orm import declarative_base, relationship
from sqlalchemy.dialects.postgresql import UUID
import uuid

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    username = Column(String(100), unique=True, nullable=False)
    email = Column(String(255), unique=True, nullable=False)
    
    # Relationship с CASCADE delete
    orders = relationship(
        'Order',
        back_populates='user',
        cascade='all, delete-orphan'  # delete-orphan удаляет если нет reference
    )

class Order(Base):
    __tablename__ = 'orders'
    
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    user_id = Column(UUID(as_uuid=True), ForeignKey('users.id'), nullable=False)
    product_name = Column(String(255))
    amount = Column()
    
    user = relationship('User', back_populates='orders')
    payments = relationship(
        'Payment',
        back_populates='order',
        cascade='all, delete-orphan'
    )

class Payment(Base):
    __tablename__ = 'payments'
    
    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    order_id = Column(UUID(as_uuid=True), ForeignKey('orders.id'), nullable=False)
    status = Column(String(20))
    amount = Column()
    
    order = relationship('Order', back_populates='payments')

Использование:

from sqlalchemy import create_engine
from sqlalchemy.orm import Session

engine = create_engine('postgresql://user:password@localhost/db')

with Session(engine) as session:
    # Создание пользователя с заказами
    user = User(username='john', email='john@example.com')
    order = Order(product_name='Laptop', amount=999.99)
    payment = Payment(status='pending', amount=999.99)
    
    order.payments.append(payment)
    user.orders.append(order)
    
    session.add(user)
    session.commit()
    
    # Удаление пользователя (благодаря CASCADE)
    session.delete(user)
    session.commit()
    
    # Проверка: заказы и платежи также удалены
    order_count = session.query(Order).filter_by(user_id=user.id).count()
    print(order_count)  # 0

6. Когда использовать CASCADE

Используй CASCADE когда:

• Дочерняя сущность не имеет смысла без родителя (заказы без пользователя)
• Удаление должно быть каскадным (комментарии при удалении поста)
• Это улучшает целостность данных

-- Хороший пример: комментарии к посту
CREATE TABLE posts (id UUID PRIMARY KEY);
CREATE TABLE comments (
    id UUID PRIMARY KEY,
    post_id UUID REFERENCES posts(id) ON DELETE CASCADE
);
-- Комментарий не существует без поста

НЕ используй CASCADE когда:

• Дочерняя сущность может существовать независимо (продукты и заказы)
• Удаление без уведомления опасно
• Нужна явная проверка перед удалением

-- Плохой пример: удалили категорию — потеряли все товары
CREATE TABLE categories (id UUID PRIMARY KEY);
CREATE TABLE products (
    id UUID PRIMARY KEY,
    category_id UUID REFERENCES categories(id) ON DELETE CASCADE  -- ОПАСНО!
);
-- Лучше использовать ON DELETE SET NULL или RESTRICT

7. Проверка существующих constraints

-- PostgreSQL: просмотр всех foreign keys
SELECT 
    tc.table_name,
    kcu.column_name,
    ccu.table_name AS foreign_table_name,
    ccu.column_name AS foreign_column_name,
    rc.delete_rule
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
JOIN information_schema.referential_constraints rc ON rc.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

Резюме

CASCADE в SQL — это мощный инструмент для автоматического каскадного удаления связанных записей:

• ON DELETE CASCADE — удалить детей
• ON DELETE RESTRICT — запретить удаление
• ON DELETE SET NULL — обнулить reference
• ON UPDATE CASCADE — обновить reference

Это упрощает управление данными, но требует осторожности: удаление может затронуть много записей.