Ограничения SQL: как проверить NOT NULL, UNIQUE и внешние ключи на тестовых данных

Проверка ограничений в SQL часто ломается не на синтаксисе, а на методике. Разработчик создает таблицу, вставляет одну-две строки, видит, что ошибок нет, и считает схему готовой. Но ограничения проверяют не «в среднем», а на конкретных краевых случаях: пустое обязательное поле, повтор уникального значения, ссылка на несуществующую запись. Поэтому полезнее не гадать, а заранее собрать матрицу допустимых и недопустимых строк и пройти ее как чек-лист.

Ниже — учебный способ проверить три базовых ограничения: NOT NULL, UNIQUE и FOREIGN KEY. Пример выполнен в PostgreSQL 18, потому что приложенный первичный источник подробно описывает поведение этих ограничений. Логика теста переносится и на другие СУБД, но тексты ошибок и некоторые детали могут отличаться.

Правила учебной модели

Для такой проверки не нужна большая предметная область. Наоборот, чем меньше сущностей, тем легче понять, какое правило сработало. Возьмем две таблицы: учебные группы и слушателей. В родительской таблице группа имеет обязательный и уникальный код. В дочерней таблице слушатель имеет обязательный уникальный email, обязательное имя и необязательную ссылку на группу.

УЧЕБНЫЙ ПРИМЕР. Цель примера — не спроектировать идеальную реальную базу, а отделить три проверки друг от друга. Поэтому group_id в таблице слушателей специально сделан nullable: так видно, что внешний ключ и требование «поле обязательно» — это разные правила.

CREATE TABLE study_groups (
    group_id integer PRIMARY KEY,
    code text NOT NULL UNIQUE
);

CREATE TABLE learners (
    learner_id integer PRIMARY KEY,
    email text NOT NULL UNIQUE,
    full_name text NOT NULL,
    group_id integer REFERENCES study_groups (group_id)
);

В этой схеме правила читаются однозначно. study_groups.code не может быть пустым и не должен повторяться. learners.email тоже обязателен и уникален. learners.full_name обязателен. А вот learners.group_id может быть NULL, но если значение задано, оно должно существовать в study_groups.group_id.

Это разделение важно для теста. Если сделать group_id одновременно и NOT NULL, и внешним ключом, то на строке с пустой ссылкой вы увидите только один отказ и не поймете, что именно проверяли. Для учебной матрицы лучше, чтобы каждая строка нарушала ровно одно правило.

Набор нарушающих записей

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

Порядок здесь не случаен. Кейс G1 должен пройти раньше, чем G3, иначе повторять будет нечего. Кейс L1 нужен раньше L3 по той же причине: сначала создаем запись с email, потом пробуем нарушить уникальность. Для внешнего ключа L4 мы специально используем 99, которого нет в таблице групп.

Последняя строка, L5, нужна не для отказа, а для проверки границы. Она показывает распространенный нюанс: внешний ключ не делает поле обязательным сам по себе. Если столбец допускает NULL, то пустое значение может пройти, а вот несуществующий идентификатор — нет. Такой кейс помогает не перепутать бизнес-правило «группа может отсутствовать» и техническое правило «если группа указана, она должна существовать».

Проверка ограничений

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

BEGIN;

-- Базовые допустимые строки
INSERT INTO study_groups (group_id, code)
VALUES (1, 'SQL-101');

INSERT INTO learners (learner_id, email, full_name, group_id)
VALUES (101, 'anna@example.test', 'Анна Ильина', 1);

-- G2: ожидаемый отказ по NOT NULL
SAVEPOINT sp_g2;
INSERT INTO study_groups (group_id, code)
VALUES (2, NULL);
ROLLBACK TO SAVEPOINT sp_g2;

-- G3: ожидаемый отказ по UNIQUE
SAVEPOINT sp_g3;
INSERT INTO study_groups (group_id, code)
VALUES (3, 'SQL-101');
ROLLBACK TO SAVEPOINT sp_g3;

-- L2: ожидаемый отказ по NOT NULL
SAVEPOINT sp_l2;
INSERT INTO learners (learner_id, email, full_name, group_id)
VALUES (102, NULL, 'Борис Смирнов', 1);
ROLLBACK TO SAVEPOINT sp_l2;

-- L3: ожидаемый отказ по UNIQUE
SAVEPOINT sp_l3;
INSERT INTO learners (learner_id, email, full_name, group_id)
VALUES (103, 'anna@example.test', 'Вера Павлова', 1);
ROLLBACK TO SAVEPOINT sp_l3;

-- L4: ожидаемый отказ по FOREIGN KEY
SAVEPOINT sp_l4;
INSERT INTO learners (learner_id, email, full_name, group_id)
VALUES (104, 'vera@example.test', 'Вера Павлова', 99);
ROLLBACK TO SAVEPOINT sp_l4;

-- L5: допустимая строка, group_id не задан
INSERT INTO learners (learner_id, email, full_name, group_id)
VALUES (105, 'boris@example.test', 'Борис Смирнов', NULL);

COMMIT;

Если вы запускаете такой сценарий вручную, логика проверки простая: на кейсах G2, G3, L2, L3 и L4 вы ожидаете ошибку, а затем возвращаетесь к точке сохранения. На G1, L1 и L5 ошибок быть не должно. Если ваш клиент останавливает выполнение после первой ошибки, прогоняйте кейсы по блокам или настройте запуск так, чтобы можно было выполнить ROLLBACK TO SAVEPOINT.

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

SELECT COUNT(*) AS groups_count FROM study_groups;
SELECT COUNT(*) AS learners_count FROM learners;

SELECT learner_id, email, full_name, group_id
FROM learners
ORDER BY learner_id;

Проверяемый результат для нашего примера такой: в study_groups останется 1 строка, а в learners — 2 строки: с learner_id = 101 и learner_id = 105. Это важнее, чем просто увидеть пять ошибок. Если после теста в таблице случайно лежит строка с дублирующимся email или несуществующей группой, значит сценарий отката был выполнен неверно.

Обработка ожидаемых отказов

У теста ограничений свой критерий успеха. Для допустимой строки успех — это вставка без ошибки. Для нарушающей строки успех — это предсказуемый отказ по запланированному правилу. Поэтому полезно заранее записать ожидаемый исход напротив каждого кейса и сравнивать не ощущения, а факт: «строка отклонена и не попала в таблицу».

Есть три частые ошибки. Первая — проверять несколько нарушений одной строкой. Например, если в записи одновременно пустой email и несуществующий group_id, вы не узнаете, какое правило сработало первым, а другое останется непроверенным. Вторая — забыть исходную строку для теста уникальности: без нее проверка дубля бессмысленна. Третья — не смотреть на итоговые данные после отказов. Ошибка могла произойти, но сценарий отката — нет.

Практический смысл матрицы в том, что она легко живет вместе со схемой. Допустим, позже вы решите, что каждый слушатель обязан быть привязан к группе, и добавите NOT NULL на learners.group_id. Тогда вам не нужно заново придумывать тесты: достаточно обновить матрицу, и кейс L5 из допустимого станет недопустимым. Так проверка превращается из разовой ручной попытки в повторяемый сценарий.

Если хотите потренировать ту же тему в Oracle, посмотрите карточку курса Oracle Database: Introduction to SQL. В сохраненной программе каталога и на официальной странице курса указаны темы создания таблиц, ограничений целостности, изменения ограничений, работы со словарем данных, а также использование Oracle SQL Developer и SQL*Plus. Это полезно, если вам нужно перенести привычку тестировать ограничения из учебной схемы в другую СУБД.