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

1. Почему проверки в коде бэкенда недостаточно?

if (checkFreeSlot(room, start, end)) {
    db.insertBooking(room, start, end);
}

2. Пирамида ограничений целостности

3. Ограничения доменов: переиспользуемая валидация

Вместо многократного дублирования CHECK-выражений в десятках таблиц проектировщик определяет предметный домен:

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

-- Домен для государственного номера автомобиля РФ
CREATE DOMAIN car_plate_ru AS VARCHAR(9)
CHECK (VALUE ~ '^[АВЕКМНОРСТУХ]\d{3}[АВЕКМНОРСТУХ]{2}\d{2,3}$');

Преимущество: при изменении нормативных требований достаточно обновить одно доменное правило, и оно мгновенно применится ко всем таблицам системы.

4. Предикаты CHECK с регулярными выражениями (POSIX regex)

-- Проверка формата СНИЛС (ХХХ-ХХХ-ХХХ ХХ)
ALTER TABLE users ADD CONSTRAINT chk_snils 
    CHECK (snils ~ '^\d{3}-\d{3}-\d{3} \d{2}$');

5. Трехзначная логика SQL: подводные камни CHECK

6. Аномалии бронирования: почему UNIQUE бессилен?

Классическое ограничение UNIQUE (room_id, start_time) запрещает только полное совпадение момента старта, но бессильно против частичного наложения интервалов:

Вывод: проверка равенства (=) неспособна выразить перекрытие интервалов (&&). Требуется специализированный механизм интервальных ограничений.

7. Интервальные типы: tsrange и daterange

-- Пример: полуоткрытый интервал [включено, не включено)
SELECT tsrange('2026-10-01 10:00', '2026-10-01 12:00', '[)') && 
       tsrange('2026-10-01 11:30', '2026-10-01 13:00', '[)'); -- Результат: TRUE (конфликт!)

8. Ограничения исключения: EXCLUDE USING gist

Синтаксис ограничения исключения задает пары «столбец + оператор»: для любых двух строк хотя бы одно из условий обязано вернуть FALSE.

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE conference_bookings (
    booking_id BIGSERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    slot TSRANGE NOT NULL,
    organizer VARCHAR(100) NOT NULL,
    
    -- Проверка корректности интервала (начало строго раньше конца)
    CONSTRAINT chk_slot_order CHECK (lower(slot) < upper(slot)),
    
    -- Исключение: для одного room_id (=) интервалы slot НЕ должны пересекаться (&&)
    CONSTRAINT exc_no_overlap EXCLUDE USING gist (
        room_id WITH =,
        slot WITH &&
    )
);

9. Почему именно GiST-индекс?

10. Циклические внешние ключи: парадокс «курицы и яйца»

Проблема взаимно зависимых сущностей в проектировании схемы:

11. Режимы работы DEFERRABLE в транзакции

SET CONSTRAINTS fk_name DEFERRED;

12. Эталонный скрипт циклической вставки

-- Создание отложенного внешнего ключа
ALTER TABLE hotels ADD CONSTRAINT fk_hotel_suite
    FOREIGN KEY (presidential_suite_id) REFERENCES rooms(room_id)
    DEFERRABLE INITIALLY DEFERRED;

-- Бесконфликтная атомарная транзакция:
BEGIN;
  -- Вставляем отель: номер 101 еще не создан, но ошибки нет!
  INSERT INTO hotels (hotel_id, name, presidential_suite_id) VALUES (1, 'Grand Palace', 101);

  -- Вставляем номер: отель 1 уже существует
  INSERT INTO rooms (room_id, hotel_id, room_number) VALUES (101, 1, 'Penthouse 101');

  -- Фиксация транзакции: СУБД проверяет fk_hotel_suite. Все сошлось!
COMMIT;

13. Аудит схемы: выявление взаимных противоречий

Ограничения не должны создавать логических тупиков. Три правила аудитора схемы:

14. Архитектурная строгость: цена небрежности в ограничениях

Тематический интернет-мем: «Exploits of a Mom» (культовый комикс про «Little Bobby Tables», xkcd №327).
Автор: Randall Munroe. Первоисточник: xkcd.com/327. Лицензия: Creative Commons Attribution-NonCommercial 2.5 Generic (CC BY-NC 2.5).
Связь с темой занятия: СУБД должна обладать непробиваемой системой внутренней защиты. Ограничения доменов, предикаты CHECK с регулярными выражениями и декларативные ограничения целостности физически не пропускают опасный или искаженный ввод в ядро базы данных, превращая любую попытку внедрения аномалий в предсказуемую синтаксическую ошибку валидации.