DDL в SQL: CREATE TABLE, ALTER TABLE, DROP — управление структурой БД

DDL (Data Definition Language) — команды для управления структурой базы данных: создание, изменение и удаление таблиц, индексов, представлений. В отличие от DML, DDL меняет схему, а не данные.

CREATE TABLE — создание таблицы

Базовый синтаксис

CREATE TABLE products (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(200) NOT NULL,
    description TEXT,
    price       NUMERIC(10, 2) NOT NULL,
    stock       INTEGER DEFAULT 0,
    category_id INTEGER,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

Типы данных (часто используемые)

Ограничения (Constraints)

CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,
    user_id     INTEGER NOT NULL,
    amount      NUMERIC(10, 2) NOT NULL CHECK (amount > 0),
    status      VARCHAR(20) NOT NULL DEFAULT 'pending'
                    CHECK (status IN ('pending', 'completed', 'refunded')),
    coupon_code VARCHAR(20) UNIQUE,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- Внешний ключ
    CONSTRAINT fk_orders_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

ON DELETE / ON UPDATE для внешних ключей

FOREIGN KEY (user_id) REFERENCES users(id)
    ON DELETE CASCADE      -- удалить заказы при удалении пользователя
    ON DELETE SET NULL     -- user_id = NULL при удалении пользователя
    ON DELETE RESTRICT     -- запретить удаление пользователя, если есть заказы
    ON DELETE NO ACTION    -- то же что RESTRICT, проверяется в конце транзакции

IF NOT EXISTS

CREATE TABLE IF NOT EXISTS categories (
    id   SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);
-- Не выдаёт ошибку, если таблица уже существует

ALTER TABLE — изменение структуры

Добавить столбец

ALTER TABLE products
ADD COLUMN weight_kg NUMERIC(6, 2);

-- С умолчанием
ALTER TABLE products
ADD COLUMN is_featured BOOLEAN NOT NULL DEFAULT false;

В PostgreSQL 11+ ADD COLUMN ... DEFAULT constant работает мгновенно (не переписывает таблицу). Изменяемые DEFAULT (NOW()) требуют перезаписи.

Удалить столбец

ALTER TABLE products
DROP COLUMN description;

-- Если есть зависимые объекты (views, foreign keys):
ALTER TABLE products
DROP COLUMN old_category CASCADE;

Изменить тип столбца

ALTER TABLE products
ALTER COLUMN price TYPE BIGINT;

-- С явным приведением
ALTER TABLE events
ALTER COLUMN metadata TYPE JSONB USING metadata::jsonb;

Переименовать столбец

ALTER TABLE users
RENAME COLUMN full_name TO display_name;

Переименовать таблицу

ALTER TABLE user_data RENAME TO users;

Добавить/удалить ограничения

-- Добавить NOT NULL
ALTER TABLE products
ALTER COLUMN name SET NOT NULL;

-- Убрать NOT NULL
ALTER TABLE products
ALTER COLUMN description DROP NOT NULL;

-- Добавить CHECK
ALTER TABLE products
ADD CONSTRAINT chk_price_positive CHECK (price > 0);

-- Убрать ограничение
ALTER TABLE products
DROP CONSTRAINT chk_price_positive;

-- Добавить UNIQUE
ALTER TABLE users
ADD CONSTRAINT uq_users_email UNIQUE (email);

-- Добавить внешний ключ
ALTER TABLE orders
ADD CONSTRAINT fk_orders_user FOREIGN KEY (user_id) REFERENCES users(id);

Изменить DEFAULT

ALTER TABLE orders
ALTER COLUMN status SET DEFAULT 'pending';

ALTER TABLE orders
ALTER COLUMN status DROP DEFAULT;

DROP — удаление объектов

-- Удалить таблицу
DROP TABLE products;

-- Если существует
DROP TABLE IF EXISTS temp_data;

-- С зависимостями (views, foreign keys)
DROP TABLE categories CASCADE;

-- Удалить несколько таблиц
DROP TABLE IF EXISTS sessions, logs, temp_imports;

⚠️ DROP TABLE удаляет данные необратимо. Всегда делайте бэкап или переименовывайте перед удалением.

Безопасные изменения схемы в продакшне

Изменения схемы на нагруженной базе могут блокировать таблицу. Вот безопасные подходы:

Добавление индекса без блокировки

-- Обычный CREATE INDEX блокирует таблицу на время создания
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- CONCURRENTLY — не блокирует, но дольше и не работает в транзакции
CREATE INDEX CONCURRENTLY idx_orders_user_id ON orders(user_id);

NOT VALID для внешних ключей

-- Добавить FK без проверки существующих строк (быстро)
ALTER TABLE orders
ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id)
NOT VALID;

-- Проверить в фоне (без блокировки INSERT/UPDATE)
ALTER TABLE orders VALIDATE CONSTRAINT fk_user;

Паттерн для переименования столбца

Переименование ломает существующий код. Безопасно:

1. Добавить новый столбец
2. Скопировать данные
3. Добавить триггер синхронизации
4. Обновить код приложения
5. Удалить старый столбец

-- Шаг 1: добавить новый столбец
ALTER TABLE users ADD COLUMN display_name TEXT;

-- Шаг 2: скопировать данные
UPDATE users SET display_name = full_name;

-- Шаг 3: в приложении переключиться на display_name

-- Шаг 4: удалить старый
ALTER TABLE users DROP COLUMN full_name;

Схемы в PostgreSQL

Таблицы живут в схемах (namespaces):

-- Создать схему
CREATE SCHEMA analytics;

-- Создать таблицу в схеме
CREATE TABLE analytics.events (
    id         BIGSERIAL PRIMARY KEY,
    event_type VARCHAR(50),
    payload    JSONB,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Путь поиска схем
SET search_path = analytics, public;

Временные таблицы

-- Живут только в текущей сессии
CREATE TEMP TABLE temp_results AS
SELECT user_id, SUM(amount) AS total
FROM orders
GROUP BY user_id;

-- Использовать как обычную таблицу
SELECT * FROM temp_results WHERE total > 10000;
-- Автоматически удаляется при закрытии сессии

Итог

DDL в продакшне требует осторожности — блокировки, зависимости, откаты. Используйте инструменты миграций (Liquibase, Flyway, Alembic) для управления изменениями схемы.