Теоретический конспект: Средства проектирования структур БД — ограничения

Фундаментальные механизмы декларативной целостности: ограничения доменов, сложные CHECK-предикаты с регулярными выражениями POSIX regex, ограничения исключения EXCLUDE USING gist и транзакционная отложенность внешних ключей DEFERRABLE.

Введение: Почему декларативные ограничения незаменимы?

В современной промышленной разработке программных комплексов существует опасное заблуждение: «База данных — это просто пассивное хранилище (dumb storage), а вся валидация и бизнес-логика должны находиться в коде бэкенд-приложения».

Архитектурная практика показывает, что такой подход неизбежно приводит к компрометации данных (Data Corruption). Причины просты:

1. Параллельные транзакции (Race Conditions): если два пользователя одновременно пытаются забронировать один и тот же зал на одно и то же время, обе сессии выполнят запрос SELECT COUNT(*) WHERE ..., получат 0 (свободно) и обе успешно выполнят INSERT. В результате в базе образуется овербукинг (двойное бронирование). Защитить от этого без блокировок уровня СУБД или ограничений целостности невозможно.
2. Множественность клиентов: к одной базе данных обращаются веб-сервисы, мобильные API, фоновые воркеры, аналитические ETL-скрипты и утилиты прямого доступа администратора. Проверка в одном коде не гарантирует отсутствие ошибок в другом.
3. Декларативность против императивности: декларативное ограничение формулирует инвариант состояния данных, за выполнением которого следит ядро СУБД с оптимизированными структурами индексов и механизмом многоверсионности (MVCC).

1. Доменные ограничения и предикаты CHECK со сложной логикой

1.1. Пользовательские домены (CREATE DOMAIN)

Домен в реляционной СУБД — это семантический тип данных. Вместо многократного повторения проверки формата в каждой таблице проектировщик создает единый именованный домен:

-- Определение домена для корректного адреса электронной почты
CREATE DOMAIN email_address AS VARCHAR(255)
CHECK (
    VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'
);

-- Определение домена для СНИЛС (11 цифр в формате ХХХ-ХХХ-ХХХ ХХ)
CREATE DOMAIN snils_code AS CHAR(14)
CHECK (
    VALUE ~ '^\d{3}-\d{3}-\d{3} \d{2}$'
);

1.2. Регулярные выражения POSIX в предикатах CHECK

Стандарт SQL:1999 и СУБД PostgreSQL предоставляют мощный аппарат сопоставления строковых атрибутов с регулярными выражениями:

1.3. Составные предикаты CHECK и трехзначная логика SQL

Ограничение CHECK может связывать значения нескольких столбцов одного кортежа:

CREATE TABLE marketplace_products (
    product_id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    base_price NUMERIC(12, 2) NOT NULL,
    discount_price NUMERIC(12, 2),
    discount_percent NUMERIC(5, 2),
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    
    -- 1. Базовая цена строго положительна
    CONSTRAINT chk_base_price_positive 
        CHECK (base_price > 0),
        
    -- 2. Процент скидки в диапазоне от 0 до 90%
    CONSTRAINT chk_discount_percent_range 
        CHECK (discount_percent IS NULL OR (discount_percent >= 0 AND discount_percent <= 90)),
        
    -- 3. Цена со скидкой строго меньше базовой цены
    CONSTRAINT chk_discount_less_than_base 
        CHECK (discount_price IS NULL OR discount_price < base_price),
        
    -- 4. Взаимная непротиворечивость: если указана скидочная цена, должен быть указан процент
    CONSTRAINT chk_discount_pair_consistency
        CHECK ((discount_price IS NULL AND discount_percent IS NULL) OR 
               (discount_price IS NOT NULL AND discount_percent IS NOT NULL))
);

Важнейшее свойство трехзначной логики SQL (TRUE, FALSE, UNKNOWN):

В отличие от предложения WHERE (которое оставляет только кортежи, где условие строго TRUE), ограничение CHECK пропускает кортеж, если результат выражения равен TRUE или UNKNOWN. Ограничение отвергает вставку только тогда, когда условие явно возвращает FALSE.

Если поле discount_price содержит NULL, то выражение discount_price < base_price вернет UNKNOWN. Запись успешно попадет в таблицу. Если бизнес-правило требует обязательного наличия значения, столбец должен быть помечен ограничением NOT NULL.

2. Предотвращение аномалий бронирования: EXCLUDE USING gist

2.1. Анатомия проблемы овербукинга

Представим систему резервирования ресурсов (конференц-залы, гостиничные номера, аренда оборудования, слоты врачебного приема). Каждый заказ задается интервалом времени [ts_start, ts_end).

Два заказа для одного ресурса конфликтуют, если их временные отрезки перекрываются хотя бы на одну секунду:

2.2. Интервальные типы данных PostgreSQL

PostgreSQL содержит встроенную поддержку диапазонов:

• tsrange — диапазон отметок времени без часового пояса (timestamp without time zone);
• tstzrange — диапазон отметок времени с часовым поясом (timestamp with time zone);
• daterange — диапазон календарных дат;
• numrange, int4range, int8range — числовые интервалы.

Диапазоны задаются скобками: квадратная скобка [ означает включение границы (inclusive), круглая ( или ) — невключение границы (exclusive). Например, '[2026-10-01 10:00, 2026-10-01 12:00)'::tsrange включает точку 10:00 и исключает 12:00, что позволяет идеально стыковать брони без зазора.

2.3. Синтаксис и механика EXCLUDE USING gist

Для индексации многомерных объектов и интервалов традиционное B-Tree дерево непригодно, так как оно поддерживает только линейный порядок (меньше, равно, больше). Для интервалов применяется обобщенное поисковое дерево GiST (Generalized Search Tree).

Для объединения скалярных полей (например, идентификатора зала room_id) и интервалов в одном GiST-индексе подключается модуль ядра btree_gist:

-- Шаг 1. Активация расширения btree_gist
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- Шаг 2. Создание таблицы конференц-залов с гарантией непересечения
CREATE TABLE room_reservations (
    reservation_id BIGSERIAL PRIMARY KEY,
    room_id INTEGER NOT NULL,
    reserved_slot TSRANGE NOT NULL,
    organizer_fio VARCHAR(120) NOT NULL,
    
    -- Защита от пустых или вырожденных интервалов (начало < конец)
    CONSTRAINT chk_valid_interval 
        CHECK (NOT isempty(reserved_slot) AND lower(reserved_slot) < upper(reserved_slot)),
        
    -- Ограничение взаимного исключения:
    -- Для одного room_id (оператор =) интервалы reserved_slot НЕ должны перекрываться (оператор &&)
    CONSTRAINT exc_room_no_double_booking 
        EXCLUDE USING gist (
            room_id WITH =,
            reserved_slot WITH &&
        )
);

3. Механика работы отложенных внешних ключей (DEFERRABLE)

3.1. Циклические графы зависимостей: проблема «курицы и яйца»

Рассмотрим реальную бизнес-модель отельного бизнеса:

• Сущность Отель (таблица hotels): каждый отель имеет свой представительский номер (президентский люкс) — атрибут presidential_suite_id.
• Сущность Номер отеля (таблица rooms): каждый номер физически принадлежит конкретному отелю — атрибут hotel_id.

Если оба поля объявлены как NOT NULL, система попадает в неразрешимый дедлок вставки. Единственное архитектурно строгое решение без ослабления ограничений до NULL — отложенная проверка внешнего ключа (Deferred Constraints).

3.2. Режимы работы DEFERRABLE

3.3. Эталонная реализация циклической схемы

-- 1. Таблица отелей (ссылается на номер, проверка отложена)
CREATE TABLE hotels (
    hotel_id INT PRIMARY KEY,
    hotel_name VARCHAR(100) NOT NULL,
    presidential_suite_id INT NOT NULL
);

-- 2. Таблица номеров (ссылается на отель)
CREATE TABLE rooms (
    room_id INT PRIMARY KEY,
    hotel_id INT NOT NULL REFERENCES hotels(hotel_id) ON DELETE CASCADE,
    room_number VARCHAR(20) NOT NULL,
    category VARCHAR(50) NOT NULL
);

-- 3. Добавление отложенного внешнего ключа к отелям
ALTER TABLE hotels
    ADD CONSTRAINT fk_hotel_presidential_suite
    FOREIGN KEY (presidential_suite_id) 
    REFERENCES rooms(room_id)
    DEFERRABLE INITIALLY DEFERRED;

-- 4. Атомарная бесконфликтная вставка в транзакции:
BEGIN;
    -- Шаг 1: Вставляем отель (ссылка на номер 501 пока висит в воздухе, ошибки нет!)
    INSERT INTO hotels (hotel_id, hotel_name, presidential_suite_id)
    VALUES (10, 'Grand Royal Resort', 501);

    -- Шаг 2: Вставляем номер (ссылается на уже созданный отель 10)
    INSERT INTO rooms (room_id, hotel_id, room_number, category)
    VALUES (501, 10, 'Penthouse 501', 'Presidential Suite');

    -- Шаг 3: Фиксация транзакции. 
    -- СУБД проверяет fk_hotel_presidential_suite: номер 501 найден!
COMMIT;

4. Выявление скрытых конфликтов и логических противоречий

При проектировании системы ограничений инженер обязан проверять предикаты на логическую совместность (Satisfiability). Ниже приведены частые скрытые дефекты:

5. Вопросы для самопроверки

1. В чем состоит принципиальное отличие ограничения UNIQUE от ограничения EXCLUDE USING gist? Какие типы данных и операторы можно использовать в EXCLUDE?
2. Почему обычный запрос SELECT COUNT(*) перед INSERT не спасает от овербукинга в высоконагруженных многопользовательских веб-приложениях?
3. Как вычисляется булево выражение в предикате CHECK при наличии значений NULL? В каких случаях кортеж отвергается СУБД?
4. Зачем в регулярных выражениях ограничений CHECK обязательно использовать символы ^ и $? Что произойдет, если их опустить?
5. Опишите пошагово сценарий возникновения циклической ссылки между таблицами. Почему режим DEFERRABLE INITIALLY DEFERRED решает проблему «курицы и яйца»?
6. Можно ли объявить ограничение CHECK как DEFERRABLE в стандартной реляционной СУБД? Поясните причину.