Теоретический конспект студента

Создание, модификация и удаление таблиц в реляционных СУБД

Дисциплина: ОП.08 Основы проектирования баз данных | Тема 5. Организация запросов SQL (§2.2 с.7–8 программы) | СИЭУиП

1. Введение в DDL: Системный каталог и объекты базы данных

Язык SQL традиционно подразделяется на несколько функциональных подмножеств. Если DML (Data Manipulation Language) отвечает за операции над строками внутри уже существующих таблиц (SELECT, INSERT, UPDATE, DELETE), то DDL (Data Definition Language) управляет самим мета-слоем — структурой базы данных, ее типами, схемами, отношениями, индексами и ограничениями целостности.

В современных объектно-реляционных СУБД (PostgreSQL) таблица не является изолированным текстовым описанием. Каждое отношение представляет собой запись в системных каталогах ядра:

┌────────────────────────────────────────────────────────────────────────┐ │ СИСТЕМНЫЙ КАТАЛОГ СУБД │ │ │ │ pg_class (OID: 16450, relname: 'warehouses', relfilenode: 28941) │ │ │ │ │ ├──► pg_attribute (warehouse_id, name, city, usable_area_sqm) │ │ │ │ │ ├──► pg_constraint (pk_warehouses, chk_area_positive) │ │ │ │ │ └──► Дисковый файл кучи (Heap Page 0, Page 1, ... Page N) │ └────────────────────────────────────────────────────────────────────────┘

Когда вы выполняете команду DDL, ядро СУБД обновляет строки в системных таблицах каталога, проверяет совместимость существующих кортежей с новыми правилами и, при необходимости, перестраивает дисковые страницы данных.

2. Оператор CREATE TABLE: Полная спецификация и лучшие практики

Оператор CREATE TABLE аллоцирует новую таблицу. В промышленном проектировании базовый синтаксис дополняется атрибутами отказоустойчивости, значениями по умолчанию и декларативными ограничениями.

2.1. Анатомия промышленного запроса CREATE TABLE

CREATE TABLE IF NOT EXISTS warehouses (
    -- 1. Суррогатный первичный ключ
    warehouse_id SERIAL PRIMARY KEY,
    
    -- 2. Строковые атрибуты фиксированной и переменной длины
    warehouse_code VARCHAR(20) NOT NULL,
    warehouse_name VARCHAR(150) NOT NULL,
    city VARCHAR(80) NOT NULL,
    
    -- 3. Точные числовые типы с фиксированной точкой для физических величин
    total_area_sqm NUMERIC(10, 2) NOT NULL,
    
    -- 4. Логические флаги с дефолтным значением
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    
    -- 5. Метки времени для аудита жизненного цикла записи
    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- 6. Именованные ограничения уровня таблицы
    CONSTRAINT unq_warehouse_code UNIQUE (warehouse_code),
    CONSTRAINT chk_total_area_positive CHECK (total_area_sqm > 0.00)
);

2.2. Создание временных таблиц: CREATE TEMPORARY TABLE

Временные таблицы создаются с ключевым словом TEMPORARY (или TEMP). Они кардинально отличаются от стандартных постоянных отношений:

-- Создание временного буфера для разбора поступившей партии
CREATE TEMPORARY TABLE temp_incoming_shipment (
    barcode VARCHAR(64) NOT NULL,
    quantity INT NOT NULL CHECK (quantity > 0)
) ON COMMIT PRESERVE ROWS;

3. Модификация структуры: Семейство операторов ALTER TABLE

В процессе промышленной эксплуатации база данных неизбежно эволюционирует. Оператор ALTER TABLE позволяет изменять топологию отношения без удаления данных.

3.1. Табличные примеры синтаксиса и назначения

Операция ALTER TABLE Синтаксический шаблон SQL Бизнес-сценарий применения
ADD COLUMN ALTER TABLE t ADD COLUMN col_name TYPE DEFAULT val [NOT NULL]; Появление нового атрибута сущности (например, добавление поля temperature_zone).
DROP COLUMN ALTER TABLE t DROP COLUMN col_name [CASCADE]; Удаление устаревшего реквизита. С опцией CASCADE удаляет зависимые представления и индексы.
ALTER COLUMN TYPE (c USING) ALTER TABLE t ALTER COLUMN col TYPE new_t USING col::new_t; Масштабирование разрядности (из INTEGER в NUMERIC(10,2)) или конвертация строки в дату.
ADD CONSTRAINT ALTER TABLE t ADD CONSTRAINT name FOREIGN KEY (col) REFERENCES p(id); Ужесточение правил целостности данных или связывание независимых таблиц внешним ключом.
DROP CONSTRAINT ALTER TABLE t DROP CONSTRAINT constraint_name; Снятие устаревшего ограничения уникальности или внешнего ключа для рефакторинга схемы.
RENAME COLUMN / TABLE ALTER TABLE t RENAME COLUMN old_c TO new_c;
ALTER TABLE t RENAME TO new_name;		 Рефакторинг терминологии предметной области (например, унификация именования атрибутов).

3.2. Роль выражения USING при конвертации типов

Когда вы меняете тип столбца, ядро СУБД должно перекодировать существующие бинарные данные кортежей на всех страницах диска. Если автоматическое неявное приведение (implicit cast) отсутствует, СУБД возвращает ошибку:

-- ОШИБКА: колонка "price" не может быть автоматически приведена к типу numeric
ALTER TABLE hotel_rooms ALTER COLUMN price TYPE NUMERIC(10, 2);

Конструкция USING передает парсеру явную функцию преобразования. Это может быть как простое приведение через оператор ::, так и сложная логика с функциями обработки строк:

-- Преобразование целого числа в точное десятичное:
ALTER TABLE hotel_rooms 
    ALTER COLUMN price TYPE NUMERIC(10, 2) 
    USING price::NUMERIC(10, 2);

-- Преобразование текстовой даты из формата 'DD.MM.YYYY' в системный DATE:
ALTER TABLE contracts 
    ALTER COLUMN signing_date TYPE DATE 
    USING to_date(signing_date, 'DD.MM.YYYY');

4. Схемы эволюции структуры без простоя сервиса (Zero-Downtime Migration)

В высоконагруженных промышленных системах (банковский эквайринг, маркетплейсы) недопустимо останавливать прием клиентских запросов для обновления схемы. Применение наивного ALTER TABLE приводит к захвату блокировки AccessExclusiveLock, которая парализует все входящие запросы SELECT и INSERT.

Этап 1: Добавление нового столбца без блокировки чтения

Вместо изменения существующего столбца «на месте» с тяжелой перезаписью дисковых страниц добавляется новый столбец с константным значением по умолчанию:

ALTER TABLE hotel_rooms ADD COLUMN price_numeric NUMERIC(10, 2) DEFAULT 0.00;

В PostgreSQL 11+ это действие модифицирует только метаданные каталога за долю миллисекунды ($O(1)$).

Этап 2: Двусторонний триггер синхронизации (Dual-Writing)

Создается триггер, который при любых операциях INSERT или UPDATE автоматически дублирует данные из старой колонки в новую. Теперь приложение может писать в старую колонку, но новая колонка всегда актуальна.

Этап 3: Фоновая миграция исторических данных (Backfill)

Небольшими порциями (батчами по 1000 строк) исторические данные копируются из старого столбца в новый. Это предотвращает длительные транзакции и переполнение журнала WAL:

UPDATE hotel_rooms SET price_numeric = price::NUMERIC(10, 2) 
WHERE room_id BETWEEN 1 AND 1000 AND price_numeric = 0.00;
Этап 4: Безопасное наложение ограничений (NOT VALID + VALIDATE)

Если на новую колонку требуется наложить ограничение CHECK, наивный ADD CONSTRAINT заблокирует таблицу для полной проверки. Инженерное решение разбивает операцию на два шага:

-- Шаг 4.1: Мгновенное наложение правила только на НОВЫЕ строки
ALTER TABLE hotel_rooms 
    ADD CONSTRAINT chk_price_positive CHECK (price_numeric > 0) NOT VALID;

-- Шаг 4.2: Фоновая валидация существующих строк без блокировки SELECT и INSERT!
ALTER TABLE hotel_rooms 
    VALIDATE CONSTRAINT chk_price_positive;
Этап 5: Переключение кода бэкенда и удаление старого столбца

После развертывания новой версии приложения, читающей из price_numeric, триггер удаляется, а старый столбец логически отключается через DROP COLUMN.

5. Диаграммы ограничений внешних ключей: CASCADE против RESTRICT

Ограничение внешнего ключа (FOREIGN KEY) связывает зависимую (дочернюю) таблицу с родительской, предотвращая появление «висячих ссылок» (orphaned records). При удалении или обновлении строки в родительской таблице стандарт SQL предусматривает различные режимы ссылочной целостности.

┌──────────────────────────┐ ┌──────────────────────────┐ │ РОДИТЕЛЬСКАЯ ТАБЛИЦА │ │ ДОЧЕРНЯЯ ТАБЛИЦА │ │ bank_clients │ │ bank_accounts │ ├──────────────────────────┤ ├──────────────────────────┤ │ client_id (PK) : 101 │◄──────────────┤ account_id (PK) : 55001 │ │ full_name : 'Иванов' │ │ │ client_id (FK) : 101 │ └──────────────────────────┘ │ │ balance : 7500.00 │ ▲ │ └──────────────────────────┘ │ │ DELETE FROM clients │ WHERE client_id = 101; │ ▼ ┌────────────────────────────────────────────────────────┐ │ Вариант А: ON DELETE RESTRICT (поведение по умолчанию) │ │ СУБД блокирует операцию! Ошибка 23503: │ │ "update or delete on table violates foreign key" │ └────────────────────────────────────────────────────────┘ │ ▼ ┌────────────────────────────────────────────────────────┐ │ Вариант Б: ON DELETE CASCADE │ │ СУБД автоматически удаляет ВСЕ счета клиента 101! │ │ Строка 55001 в bank_accounts безвозвратно удалена! │ └────────────────────────────────────────────────────────┘

5.1. Различие между ON DELETE CASCADE и DROP TABLE ... CASCADE

Критическое различие двух видов CASCADE:
  1. FOREIGN KEY ... ON DELETE CASCADE — это правило уровня манипулирования данными (DML). При удалении строки из таблицы клиентов СУБД каскадно удаляет строки счетов из дочерней таблицы. Сами таблицы остаются целыми.
  2. DROP TABLE parent_table CASCADE — это команда уровня определения данных (DDL). Она физически уничтожает саму таблицу parent_table и автоматически удаляет зависимые ограничения внешнего ключа в дочерних таблицах, а также удаляет зависимые представления (VIEW). Сами строки в дочерней таблице НЕ удаляются, но таблица лишается внешнего ключа и теряет ссылочную целостность!

6. Быстрая очистка: Оператор TRUNCATE TABLE и сброс последовательностей

Когда требуется полностью очистить таблицу (например, после завершения учебного семестра или при обновлении промежуточных логов), использование DELETE FROM table_name; является грубой инженерной ошибкой на больших объемах данных.

6.1. Почему TRUNCATE быстрее DELETE в сотни раз?

6.2. Управление генераторами последовательностей (RESTART IDENTITY)

При использовании автоинкрементных столбцов (SERIAL, BIGSERIAL, IDENTITY) таблица ассоциирована с объектом генератора последовательностей (SEQUENCE):

-- 1. Очистка данных с продолжением нумерации (CONTINUE IDENTITY по умолчанию)
-- Если последняя запись имела ID=500, то следующая вставка получит ID=501:
TRUNCATE TABLE student_attendance_log;

-- 2. Очистка данных со сбросом автоинкремента в начальное значение:
-- Счетчик последовательности сбрасывается в 1:
TRUNCATE TABLE student_attendance_log RESTART IDENTITY;

7. Вопросы для самопроверки и закрепления материала

  1. В чем заключается концептуальная разница между языками DDL и DML на уровне ядра СУБД? Какие структуры данных изменяет DDL?
  2. Почему при изменении типа данных столбца с помощью команды ALTER COLUMN TYPE часто требуется выражение USING? Приведите пример конвертации данных, где USING обязателен.
  3. Что произойдет при выполнении команды DROP TABLE customers RESTRICT;, если в базе существует дочерняя таблица orders с внешним ключом на customers?
  4. Каковы фатальные последствия неосмотрительного применения DROP TABLE ... CASCADE в промышленной базе данных? Удаляются ли данные в дочерних таблицах?
  5. По каким пяти фундаментальным критериям операция TRUNCATE TABLE превосходит DELETE FROM table? В каких ситуациях применять TRUNCATE запрещено?
  6. Какую роль играет опция RESTART IDENTITY при выполнении TRUNCATE TABLE?
  7. Какая блокировка захватывается ядром СУБД при выполнении команд DDL (ALTER TABLE)? Как эта блокировка может вызвать аварийную остановку (Outage) высоконагруженного веб-приложения?
  8. Опишите двухфазный алгоритм добавления ограничения CHECK на многомиллионной таблице без остановки сервиса.