Методическое руководство для преподавателя

Тема: Средства проектирования структур БД: ограничения (§2.2 с.7–8 учебной программы). Вид занятия: Урок с объяснением теории. 2 академических часа.

1. Дидактическая концепция и целеполагание

Ограничения целостности (Data Integrity Constraints) — это фундаментальный каркас реляционной модели, гарантирующий корректность данных на физическом и логическом уровне независимо от клиентского программного обеспечения. На занятии 22 студенты переходят от абстрактной нормализации к практическому инструментарию декларативной целостности современных промышленных СУБД (PostgreSQL / ANSI SQL-92/99/2011).

Дидактические задачи:

• Обучающие:
  • Изучить классификацию ограничений целостности: ограничения атрибутов/доменов (DOMAIN), кортежей (CHECK), таблиц (PRIMARY KEY, UNIQUE) и связей (FOREIGN KEY).
  • Освоить синтаксис и семантику расширенных предикатов CHECK с применением POSIX регулярных выражений (операторы ~, ~*, !~, !~*).
  • Разобрать математическую модель интервальных типов (tsrange, daterange, numrange) и проектирование ограничений взаимного исключения EXCLUDE USING gist с оператором пересечения && для предотвращения овербукинга и конфликтов расписания.
  • Изучить жизненный цикл транзакции и механику отложенной проверки внешних ключей DEFERRABLE INITIALLY DEFERRED / IMMEDIATE при циклических графах зависимостей (проблема «курицы и яйца»).
• Развивающие: развить алгоритмическое мышление, системный подход к превентивной защите данных от логических коллизий и способность выявлять взаимно противоречивые предикаты еще на этапе проектирования схемы.
• Воспитательные: воспитать культуру инженерной строгости (defense-in-depth), осознание недопустимости делегирования базовой целостности клиентским интерфейсам и ответственность за надежность хранения критически важных данных.

2. Детальный хронометраж учебного занятия

Материал спроектирован для двух вариантов регламента: основного (80 минут) и сокращенного (60 минут).

3. Архитектура системы ограничений целостности СУБД

Современная реляционная СУБД реализует трехуровневую пирамиду декларативной защиты:

CREATE DOMAIN email_type AS VARCHAR(255)
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');

CREATE DOMAIN snils_type AS CHAR(14)
CHECK (VALUE ~ '^\d{3}-\d{3}-\d{3} \d{2}$');

ALTER TABLE contract
  ADD CONSTRAINT chk_contract_dates
  CHECK (end_date IS NULL OR end_date >= start_date);

ALTER TABLE products
  ADD CONSTRAINT chk_pricing
  CHECK (discount_price IS NULL OR discount_price < base_price);

3.1. Регулярные выражения в CHECK-ограничениях (POSIX regex)

В стандарте SQL и PostgreSQL поддерживаются операторы сопоставления с регулярными выражениями:

• ~ — совпадение с регулярным выражением с учетом регистра;
• ~* — совпадение без учета регистра;
• !~ — не совпадает (с учетом регистра);
• !~* — не совпадает (без учета регистра).

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

Классический UNIQUE проверяет равенство значений: a = b. Однако реальные бизнес-процессы оперируют интервалами (время бронирования, периоды проживания, диапазоны портов, геометрические фигуры). Непересечение интервалов выражается оператором перекрытия &&, который невозможно реализовать через обычный B-Tree UNIQUE.

Для решения этой задачи PostgreSQL предоставляет ограничения исключения (EXCLUDE) на базе обобщенных поисковых деревьев (GiST):

-- 1. Подключение расширения для поддержки оператора = в GiST
CREATE EXTENSION IF NOT EXISTS btree_gist;

-- 2. Таблица бронирования конференц-залов
CREATE TABLE meeting_room_bookings (
    booking_id BIGSERIAL PRIMARY KEY,
    room_id INT NOT NULL,
    slot TSRANGE NOT NULL,
    booked_by VARCHAR(100) NOT NULL,
    CONSTRAINT chk_slot_bounds CHECK (lower(slot) < upper(slot)),
    CONSTRAINT exc_no_overlap EXCLUDE USING gist (
        room_id WITH =,
        slot WITH &&
    )
);

Механизм работы: СУБД гарантирует, что для любых двух строк таблицы с одинаковым room_id их интервалы slot НЕ будут пересекаться (slot1 && slot2 вернет FALSE). Попытка параллельной вставки конфликтующей брони блокируется на уровне ядра СУБД с ошибкой exclusion_violation (23P01), устраняя проблему Race Condition без ручных блокировок LOCK TABLE.

3.3. Циклические внешние ключи и отложенные ограничения (DEFERRABLE)

В архитектуре баз данных нередко возникает циклическая зависимость 1:1 или 1:N:

• Отель содержит множество номеров: rooms.hotel_id REFERENCES hotels(id).
• Отель имеет один флагманский президентский люкс: hotels.presidential_suite_id REFERENCES rooms(id).

Если оба внешних ключа являются строгими (NOT NULL), возникает парадокс блокировки: мы не можем создать отель, так как еще не создан номер-люкс; но мы не можем создать номер-люкс, так как еще не создан отель!

ALTER TABLE hotels
  ADD CONSTRAINT fk_hotel_suite
  FOREIGN KEY (presidential_suite_id) REFERENCES rooms(id)
  DEFERRABLE INITIALLY DEFERRED;

-- Бесконфликтная вставка в рамках одной транзакции:
BEGIN;
  INSERT INTO hotels (id, name, presidential_suite_id) VALUES (1, 'Grand Palace', 101);
  INSERT INTO rooms (id, hotel_id, room_number) VALUES (101, 1, 'Suite 101');
COMMIT; -- Проверка FK выполняется СТРОГО в момент COMMIT!

Режимы отложенности:

• NOT DEFERRABLE (по умолчанию) — проверка выполняется немедленно после выполнения каждого DML-оператора.
• DEFERRABLE INITIALLY IMMEDIATE — ограничение может быть отложено, но по умолчанию проверяется после каждого оператора. Переключается в отложенный режим командой SET CONSTRAINTS fk_name DEFERRED; внутри транзакции.
• DEFERRABLE INITIALLY DEFERRED — проверка автоматически откладывается до фиксации транзакции (COMMIT). Если к моменту COMMIT целостность нарушена, транзакция откатывается (ROLLBACK).

4. Типовые ошибки студентов и методы их предупреждения

5. Педагогические приемы и методические рекомендации

1. Прием «Ломаем базу данных в прямом эфире»: на этапе демонстрации преподаватель намеренно запускает два терминала psql и пытается вставить накладывающиеся брони в таблицу без EXCLUDE (успех, возникает коллизия) и в таблицу с EXCLUDE (мгновенная блокировка второй транзакции). Визуальный эффект ошибки exclusion_violation создает сильнейший эмоциональный якорь.
2. Прием «Судебный эксперт схемы»: предложить студентам выступить в роли независимого аудитора, проверяющего чужой DDL-код. Студенты должны найти «скрытые мины» (логические тупики, бесконечные циклы блокировок, пропущенные якоря regex).
3. Дифференциация заданий:
   • Вариант 1 (Залы и EXCLUDE): акцент на временную геометрию и интервалы tsrange.
   • Вариант 2 (Банковский портал и regex): акцент на государственные стандарты (СНИЛС, серия/номер паспорта) и дату совершеннолетия.
   • Вариант 3 (Отели и DEFERRABLE): архитектурная дилемма циклических зависимостей и транзакционный контекст.
   • Вариант 4 (Электронный магазин): составные предикаты скидок, артикулов SKU и ценовых диапазонов.

6. Критерии оценивания практической деятельности (4-балльная шкала)