Определите результат добавления ограничения: какая команда завершится ошибкой и почему? пример с кодом

CREATE TABLE payments (
    payment_id INTEGER PRIMARY KEY,
    amount DECIMAL(10, 2)
);

INSERT INTO payments VALUES (1, 125.00);
INSERT INTO payments VALUES (2, -5.00);

ALTER TABLE payments
ADD CONSTRAINT positive_amount CHECK (amount >= 0);

Краткий ответ

Команда ALTER TABLE завершится ошибкой, потому что существующая строка с amount = -5.00 нарушает добавляемое ограничение CHECK. Ограничение нельзя добавить в обычном режиме, пока все проверяемые существующие строки не удовлетворяют его условию.

Исторический контекст

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

Команда ALTER TABLE позволяет вводить такие правила после создания таблицы. Поэтому СУБД должна проверить, что новое правило совместимо не только с будущими, но и с уже сохранёнными данными.

Постановка проблемы

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

Неверное решение может привести к сбою миграции, частично выполненному развёртыванию схемы или необходимости срочно исправлять данные под блокировками. Точные блокировки, длительность проверки и транзакционность DDL зависят от СУБД.

Подробное решение

При добавлении CHECK СУБД проверяет условие для существующих строк. Для строки с 125.00 выражение amount >= 0 истинно, а для строки с -5.00 — ложно, поэтому добавление ограничения отклоняется.

Обычно в транзакционных СУБД неуспешная команда не оставляет новое ограничение созданным. Однако поведение DDL-транзакций и блокировок нельзя безоговорочно переносить между всеми реализациями SQL.

Надёжная последовательность миграции выглядит так:

UPDATE payments
SET amount = 0
WHERE amount < 0;

ALTER TABLE payments
ADD CONSTRAINT positive_amount CHECK (amount >= 0);

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

Некоторые СУБД поддерживают добавление ограничения без немедленной проверки старых строк. Например, в PostgreSQL есть NOT VALID: новые изменения проверяются сразу, а старые строки проверяются отдельной операцией VALIDATE CONSTRAINT. Это уменьшает длительность блокировки при добавлении правила, но временно оставляет в таблице исторические нарушения и не является переносимым синтаксисом стандартного SQL.

Ситуация из практики

В платёжной системе миграция должна была запретить отрицательные суммы. Вариант с немедленным ADD CONSTRAINT оказался непригоден: он обнаружил старые корректирующие записи, которые исторически хранились со знаком минус.

Рассматривались два подхода. Массовое исправление данных было простым, но могло уничтожить смысл корректировок. Добавление PostgreSQL-ограничения NOT VALID позволяло быстро защитить новые записи, но требовало отдельной обработки старых строк и последующей валидации.

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

Что кандидаты часто упускают

1. Что произойдёт, если в существующем столбце есть NULL?

Ответ: обычный CHECK обычно не отклоняет строку, если результат его выражения — UNKNOWN, а не FALSE. Например, amount >= 0 для NULL даёт UNKNOWN, поэтому одного CHECK недостаточно для запрета отсутствующих значений. Для этого нужно отдельно объявить amount NOT NULL или включить явную проверку, например CHECK (amount IS NOT NULL AND amount >= 0).

2. Проверяются ли новые строки после успешного добавления ограничения?

Ответ: да, ограничение становится частью схемы и применяется к последующим INSERT и UPDATE. Попытка записать отрицательную сумму будет отклонена. Важно учитывать, что выражение CHECK, использующее другие таблицы или нестабильные функции, может создавать проблемы переносимости и предсказуемости; обычно правило формулируют на основе значений текущей строки.

3. Почему нельзя просто добавить ограничение, а проверку старых данных выполнить позже без специального режима СУБД?

Ответ: стандартная семантика добавленного ограничения требует, чтобы оно описывало действительное состояние таблицы. Отложенная проверка — это расширение конкретной СУБД, например NOT VALID в PostgreSQL. Такой режим полезен для больших таблиц, но усложняет эксплуатацию: нужно отслеживать непроверенные строки, планировать валидацию и понимать, что схема временно имеет более слабую гарантию целостности.