3.07. Ограничения в SQL

ОБЯЗАТЕЛЬНОДЛЯ НОВИЧКОВНЕ ДЛЯ НОВИЧКОВВ РАЗРАБОТКЕ

Разработчику Аналитику Тестировщику
Архитектору Инженеру

Ограничения в SQL

Помню, я много лет работал с SQL, выполнял сложные большие запросы, с кучей JOIN, UNION и так далее. Создавал базы данных, наполнял, изменял, а также много работал с языком программирования C#. И вот, спустя годы, прохожу техническое собеседование с одним из лидов крупной компании. Опрос идёт уже час, и вот он задаёт вопрос "Что такое констрейны?", а я задумался - что же это...

Разумеется, это лишь было началом минуса, и собеседование было провалено, и я побежал читать про загадочные констрейны, и оказалось, что под этим словом CONSTRAINT буквально пряталось слово, которое я использовал и знал - ОГРАНИЧЕНИЯ!

То есть, я много лет работал с ограничениями (и как я мог забыть название команды?), но лишь услышав англоязычную вариацию, растерялся. Но, как говорится, с кем не бывает - мы же с вами рассмотрим тему ограничений более подробно.

Ограничения (constraints) в SQL — это декларативные правила, встроенные в определение таблиц, которые обеспечивают корректность, согласованность и надёжность данных на уровне СУБД. Они действуют независимо от клиентского приложения: даже при прямом обращении к базе данных через утилиту командной строки или административный интерфейс, ограничения предотвращают внесение данных, нарушающих заданные условия. Такой подход соответствует принципам реляционной модели данных, предложенной Эдгаром Коддом в 1970 году, где целостность данных рассматривается как фундаментальное требование. В работе Кодда особое внимание уделялось целостности домена, целостности сущности и целостности ссылок — эти три категории легли в основу современных типов ограничений, реализованных в стандартах SQL (начиная с SQL-92 и далее).

Ограничения относятся к декларативной защите данных. Это означает, что программист формулирует правило в терминах самого языка SQL, без необходимости писать императивный код (например, триггеры или процедуры), который проверял бы данные построчно при каждой операции. Декларативность упрощает проектирование, повышает предсказуемость поведения системы и сокращает вероятность ошибок, связанных с человеческим фактором. СУБД гарантирует соблюдение ограничений при любой операции вставки, обновления или удаления строк — независимо от того, кто её выполняет и каким образом.

Почему данные могут оказаться некорректными

Некорректные данные возникают при отсутствии строгих правил, регулирующих их структуру и взаимосвязи. Такие данные приводят к ошибкам в бизнес-логике, искажению аналитики, нарушению отчётности и, в конечном счёте, к потере доверия к системе. Примеры:

Отрицательная цена товара — формально допустимое числовое значение, но экономически бессмысленное. Без проверки такого условия система может рассчитать итоговую стоимость заказа со знаком минус и выполнить возврат средств при покупке.
Заказ без клиента — если идентификатор клиента в заказе может быть NULL или ссылаться на несуществующую запись, становится невозможным связать заказ с владельцем, отправить уведомление, начислить бонусы или выполнить юридически значимую операцию.
Дублирование адреса электронной почты — при регистрации пользователей без уникальности email один и тот же адрес может быть привязан к нескольким аккаунтам. Это нарушает однозначность идентификации, затрудняет восстановление доступа, позволяет обходить лимиты и создаёт уязвимости в системах аутентификации.

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

Классификация ограничений

Ограничения можно классифицировать по нескольким независимым критериям.

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

Ограничения доменной целостности регулируют допустимые значения внутри отдельного столбца: тип данных, допустимость NULL, диапазон, формат. Примеры — NOT NULL, CHECK, DEFAULT.
Ограничения сущностной целостности гарантируют уникальную идентификацию каждой строки в таблице. Основной инструмент — PRIMARY KEY, дополнительный — UNIQUE.
Ограничения ссылочной целостности обеспечивают корректность связей между таблицами. Реализуются через FOREIGN KEY.

По уровню объявления ограничения бывают:

Столбцовые — объявляются в контексте одного столбца при его определении. Например, email VARCHAR(255) NOT NULL.
Табличные — объявляются отдельно после перечисления столбцов и могут охватывать несколько столбцов сразу. Например, составной первичный ключ PRIMARY KEY (order_id, product_id) или ограничение CHECK (start_date <= end_date) с участием двух полей.

По способу именования:

Именованные ограничения получают явное имя, задаваемое разработчиком через CONSTRAINT <имя>. Это упрощает диагностику ошибок, управление (удаление, изменение) и документирование.
Анонимные ограничения создаются без явного имени; СУБД назначает им сгенерированное системное имя (например, order_pkey, order_customer_id_fkey). Такие имена трудно читать и использовать в скриптах.

Первичный ключ (PRIMARY KEY)

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

У первичного ключа есть два встроенных ограничения:

UNIQUE — все значения должны различаться.
NOT NULL — ни одно значение не может быть неопределённым.

Совокупность этих двух условий гарантирует, что каждая строка обладает стабильным, неповторимым идентификатором. В большинстве СУБД (включая PostgreSQL) объявление PRIMARY KEY автоматически создаёт B-дерево-индекс, оптимизирующий поиск по ключу и ускоряющий проверку уникальности при вставке и обновлении.

Первичный ключ может быть простым (один столбец) или составным (несколько столбцов). Простой ключ предпочтителен для упрощения связи с внешними таблицами и повышения читаемости запросов. Однако составной ключ уместен, когда сущность естественным образом определяется комбинацией атрибутов — например, запись в таблице расписания «пара» однозначно задаётся днём недели, номером пары и аудиторией.

Внешний ключ (FOREIGN KEY)

Внешний ключ — это столбец или группа столбцов в одной таблице, ссылающиеся на первичный (или уникальный) ключ в другой таблице. Основная цель внешнего ключа — поддержание ссылочной целостности. Это означает, что любое значение во внешнем ключе либо соответствует существующему значению в целевой таблице, либо является NULL (если столбец допускает NULL). Такой механизм предотвращает появление «висячих» ссылок — ситуаций, когда запись в дочерней таблице ссылается на несуществующую родительскую.

При составном внешнем ключе (несколько столбцов) поведение при частичном NULL регулируется предложением MATCH. По умолчанию применяется MATCH SIMPLE: строка считается валидной, если хотя бы один столбец внешнего ключа содержит NULL, даже если остальные столбцы ссылаются на несуществующие значения. Это соответствует принципу «отсутствие ссылки — не нарушение». Модификатор MATCH FULL требует, чтобы либо все столбцы внешнего ключа были NOT NULL и ссылались на существующую строку, либо все были NULL. Промежуточное состояние (один NULL, другой — значение) нарушает ограничение. MATCH FULL применяется, когда отношение является строго обязательным и частичное отсутствие ссылки недопустимо по бизнес-логике — например, в трёхкомпонентном составном ключе «страна-регион-город», где регион без страны не имеет смысла.

Поддержка MATCH есть в PostgreSQL, Oracle, DB2; в MySQL и SQL Server поведение фиксировано как MATCH SIMPLE, и альтернатива недоступна.

Ссылочная целостность реализуется через декларативное правило, встроенное в структуру таблицы. При попытке вставить строку со значением внешнего ключа, отсутствующим в родительской таблице, СУБД возвращает ошибку. Аналогично, при удалении или изменении строки в родительской таблице, на которую есть ссылки, поведение определяется клаузами ON DELETE и ON UPDATE.

Доступны следующие варианты поведения:

NO ACTION — стандартное поведение: операция отклоняется, если существуют зависимые строки.
RESTRICT — синоним NO ACTION в большинстве СУБД.
CASCADE — при удалении (или обновлении) родительской строки автоматически удаляются (или обновляются) все связанные дочерние строки.
SET NULL — при удалении (обновлении) родителя в дочерних строках значение внешнего ключа устанавливается в NULL (только если столбец допускает NULL).
SET DEFAULT — значение внешнего ключа заменяется на значение по умолчанию, заданное для столбца (поддерживается не во всех СУБД; в PostgreSQL — да, при условии, что DEFAULT объявлен).

В большинстве СУБД RESTRICT и NO ACTION ведут себя одинаково — операция отклоняется, если существуют зависимые строки. Однако в PostgreSQL существует различие:

NO ACTION — проверка выполняется в момент операции (или откладывается до COMMIT, если ограничение объявлено DEFERRABLE INITIALLY DEFERRED). Это позволяет временно нарушить ссылочную целостность внутри транзакции, если последующие операции её восстановят.
RESTRICT — проверка происходит немедленно и не может быть отложена. Даже при использовании SET CONSTRAINTS ALL DEFERRED ограничение RESTRICT остаётся строгим.

RESTRICT — более сильная гарантия; NO ACTION — более гибкая.

Внешний ключ без индекса работает, но приводит к значительному снижению производительности. При каждой проверке ссылочной целостности (например, при вставке в дочернюю таблицу или удалении из родительской) СУБД вынуждена выполнять полное сканирование дочерней таблицы в поисках соответствующих значений. Индекс по внешнему ключу превращает эту операцию из O(n) в O(log n), что критично при росте объёма данных.

Использование ON DELETE CASCADE требует осмотрительности. Такое поведение удобно в иерархиях «владелец — зависимые объекты» (например, «пользователь — его комментарии»), где удаление владельца логически влечёт уничтожение всего контекста. Однако в более сложных доменах (например, «заказ — позиции заказа») автоматическое каскадное удаление может привести к потере данных, имеющих самостоятельную ценность. В таких случаях предпочтителен NO ACTION с последующей ручной обработкой через приложение или служебные скрипты — это делает последствия операции явными и контролируемыми.

Объявление внешнего ключа допускает сокращённую форму: FOREIGN KEY (col) REFERENCES parent_table, без указания целевого столбца. В этом случае СУБД автоматически связывает столбец с первичным ключом родительской таблицы. Такая форма упрощает DDL и снижает риск ошибок при именовании, но требует, чтобы в родительской таблице был объявлен именно PRIMARY KEY, а не просто UNIQUE. Если используется составной первичный ключ, сокращённая форма по-прежнему корректна — столбцы ссылающегося ключа должны совпадать по количеству, порядку и типу с компонентами первичного ключа.

Уникальность (UNIQUE)

Ограничение UNIQUE гарантирует, что значения в указанном столбце (или группе столбцов) не повторяются в рамках таблицы. Оно отличается от PRIMARY KEY тем, что допускает наличие NULL-значений — и даже нескольких NULL-ов, поскольку в SQL NULL не равен NULL, а значит, несколько строк со значением NULL в уникальном столбце не нарушают условие уникальности.

Уникальность применяется в ситуациях, когда требуется идентификация по атрибуту, отличному от первичного ключа. Например:

email пользователя должен быть уникальным, хотя идентификатором служит числовой user_id;
штрихкод товара уникален, но первичный ключ — внутренний product_id;
в справочнике валют код ISO (например, RUB, USD) должен быть уникален.

Наличие нескольких уникальных ограничений в одной таблице отражает множественность способов однозначной идентификации сущности. Это соответствует реальному миру: человек может быть идентифицирован по СНИЛС, паспорту или ИНН — все три идентификатора уникальны, но ни один из них не является «главным» в абсолютном смысле.

Как и PRIMARY KEY, ограничение UNIQUE создаёт индекс (обычно B-дерево), что делает проверку эффективной и позволяет использовать столбец в условиях соединения или фильтрации без дополнительных затрат.

Запрет пустых значений (NOT NULL)

Ключевое слово NOT NULL указывает, что столбец не может содержать неопределённое значение (NULL). Это ограничение доменной целостности, направленное на обеспечение полноты данных. Оно особенно важно для атрибутов, критичных для бизнес-логики: идентификаторы, даты создания, обязательные параметры.

Пустое значение NULL в SQL интерпретируется как «отсутствие информации», а не как ноль, пустая строка или ложь. Это создаёт особенности в логических выражениях: любая операция сравнения с NULL возвращает UNKNOWN, а не TRUE или FALSE. Это поведение может нарушать ожидания приложения, особенно если используемый драйвер или ORM некорректно обрабатывает NULL (например, возвращает None, nil или исключение). Явное запрещение NULL устраняет эту неопределённость на уровне данных.

Однако чрезмерное использование NOT NULL может снизить гибкость модели. Некоторые атрибуты объективно могут отсутствовать на ранних этапах жизни сущности — например, дата отгрузки заказа неизвестна при создании, но обязательна при завершении. В таких случаях допустимость NULL отражает реальное состояние процесса. Решение о NOT NULL должно основываться на предметной области, а не на технических предпочтениях.

Особое внимание требует внешний ключ: если он объявлен без NOT NULL, то допускает отсутствие связи с родительской таблицей. Это уместно для необязательных отношений (например, «сотрудник — куратор»: не у всех сотрудников есть куратор). Если же связь обязательна, NOT NULL должен присутствовать — тогда любая строка в дочерней таблице гарантированно имеет корректную ссылку на родителя.

Проверка условия (CHECK)

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

возраст не меньше 18 лет: CHECK (age >= 18)
статус заказа может принимать только значения из списка: CHECK (status IN ('new', 'paid', 'shipped', 'canceled'))
дата окончания не раньше даты начала: CHECK (start_date <= end_date)

Ограничение CHECK оценивается при каждой операции INSERT и UPDATE. Если результат выражения — FALSE, операция отклоняется. Если результат — TRUE или UNKNOWN (например, из-за NULL в одном из операндов), операция разрешается.

Это поведение означает, что ограничение CHECK само по себе не защищает от отсутствия данных. Например, CHECK (price > 0) разрешит запись INSERT INTO products (price) VALUES (NULL), поскольку NULL > 0 даёт UNKNOWN. Для исключения неопределённых значений требуется комбинировать CHECK с NOT NULL, либо использовать явное выражение CHECK (price IS NOT NULL AND price > 0) — хотя первый способ предпочтительнее с точки зрения читаемости и семантической чистоты.

Важные особенности CHECK:

выражение не может содержать подзапросы;
оно не может ссылаться на столбцы других таблиц;
оно не может содержать недетерминированные функции (например, CURRENT_TIMESTAMP, RANDOM()) в некоторых СУБД, включая PostgreSQL — такие функции запрещены, потому что значение ограничения должно быть стабильным во времени.

В PostgreSQL CHECK-ограничения не создают индексы. Это осознанное решение: индексы оптимизируют поиск, а CHECK служит только для валидации. Если часто требуется фильтровать по условию, аналогичному CHECK, следует создать отдельный индекс вручную.

Значение по умолчанию (DEFAULT)

Ключевое слово DEFAULT задаёт значение, которое присваивается столбцу, если при вставке новой строки явное значение не указано. Это повышает удобство работы с таблицей и снижает вероятность пропусков.

Значением по умолчанию может быть:

литерал (строка, число, дата);
детерминированная функция без параметров (например, CURRENT_TIMESTAMP, gen_random_uuid());
выражение в скобках, если поддерживается СУБД (в PostgreSQL — да: DEFAULT ('user_' || nextval('user_seq'))).

DEFAULT особенно полезен для:

временных меток создания (created_at DEFAULT CURRENT_TIMESTAMP);
идентификаторов, генерируемых на стороне СУБД (UUID, sequence-based ID);
флагов и статусов по умолчанию (is_active DEFAULT true);
версий схемы (schema_version DEFAULT 1).

Значение по умолчанию применяется только при операциях INSERT. При UPDATE оно не участвует — изменение происходит только для явно указанных столбцов. Это позволяет постепенно эволюционировать данные: новые строки получают актуальные значения, старые сохраняют исторические.

Ограничения столбца и ограничения таблицы

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

Столбцовое ограничение записывается непосредственно после имени и типа столбца:

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email VARCHAR(255) NOT NULL UNIQUE,
    age INT CHECK (age >= 0)
);

Здесь PRIMARY KEY, NOT NULL, UNIQUE и CHECK — все объявлены как столбцовые. Такая форма удобна для простых правил, затрагивающих один атрибут.

Табличное ограничение выносится в отдельную строку в конце определения таблицы:

CREATE TABLE orders (
    order_id SERIAL,
    user_id INT,
    created_at TIMESTAMPTZ,
    PRIMARY KEY (order_id),
    FOREIGN KEY (user_id) REFERENCES users(id),
    CHECK (created_at <= NOW())
);

Табличные ограничения необходимы в трёх случаях:

когда ограничение охватывает несколько столбцов (например, составной первичный ключ PRIMARY KEY (user_id, event_type));
когда правило ссылается на другой столбец той же таблицы (например, CHECK (discount <= total));
когда требуется явное именование или более чёткая структуризация DDL-скрипта.

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

Именованные и анонимные ограничения

Явное именование ограничений — передовая практика проектирования. Оно достигается с помощью конструкции CONSTRAINT <имя> <тип_ограничения>:

CREATE TABLE products (
    id SERIAL,
    price NUMERIC(10,2),
    CONSTRAINT pk_products PRIMARY KEY (id),
    CONSTRAINT chk_price_positive CHECK (price > 0)
);

Преимущества именования:

Человекочитаемые сообщения об ошибках. Вместо ERROR: new row for relation "products" violates check constraint "products_price_check" — ERROR: new row violates check constraint "chk_price_positive".
Точечное управление. Удаление или изменение ограничения требует знания его имени. Имена вроде fk_order_user или uq_email делают команды ALTER TABLE … DROP CONSTRAINT предсказуемыми.
Документирование в коде. Имя служит кратким описанием назначения правила.
Избежание конфликтов при миграциях. Разные СУБД генерируют разные имена для анонимных ограничений. Явные имена гарантируют идемпотентность скриптов.

Рекомендуется использовать согласованную схему именования:

pk_<таблица> — первичный ключ;
uq_<таблица>_<столбец> — уникальность (или uq_<таблица>_<группа>);
fk_<дочерняя>_<родительская> — внешний ключ;
chk_<таблица>_<описание> — проверочное условие;
nn_<таблица>_<столбец> — запрет NULL (реже, так как NOT NULL часто встроен в определение столбца).

EXCLUDE — ограничение пересечения (PostgreSQL)

PostgreSQL предоставляет уникальное ограничение EXCLUDE, недоступное в других массовых СУБД. Оно обобщает идею UNIQUE, позволяя задавать условие отсутствия пересечения по произвольному оператору, а не только по равенству.

Классический пример — предотвращение наложения временных интервалов:

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE reservations (
    room_id INT,
    during TSRANGE,
    EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

Здесь оператор && проверяет пересечение диапазонов, а оператор = — совпадение номера комнаты. Ограничение гарантирует, что для одной и той же комнаты не может существовать двух пересекающихся бронирований.

EXCLUDE требует индекса определённого типа (обычно GiST или SP-GiST), совместимого с используемыми операторами. Это делает его мощным инструментом для пространственных данных, временных рядов и других доменов, где «уникальность» определяется не идентичностью, а отсутствием конфликта.

Взаимодействие с индексами

Ограничения и индексы тесно связаны, но не тождественны:

PRIMARY KEY автоматически создаёт уникальный B-дерево-индекс по указанным столбцам.
UNIQUE также создаёт уникальный B-дерево-индекс.
FOREIGN KEY не создаёт индекс автоматически в большинстве СУБД (включая PostgreSQL), но настоятельно рекомендуется создавать его вручную — иначе проверка ссылочной целостности при операциях в родительской таблице будет медленной.
CHECK и NOT NULL не создают индексы — они реализуются через прямую проверку при модификации строк.

Важно помнить: индекс — это структура для ускорения поиска, ограничение — правило для защиты данных. Одно поддерживает другое, но их цели различны. Например, можно создать UNIQUE-ограничение, а затем удалить созданный им индекс — ограничение останется в силе, но проверка станет медленной. Обратная операция невозможна: нельзя создать уникальный индекс без UNIQUE-ограничения и ожидать защиты от дубликатов — индекс обеспечит быстрый поиск, но не предотвратит вставку.

Отложенная проверка, NOT VALID, VALIDATE CONSTRAINT

В PostgreSQL реализованы продвинутые механизмы управления ограничениями в условиях высокой нагрузки и сложных миграций.

Отложенные (DEFERRABLE) ограничения позволяют перенести проверку с момента модификации строки на конец транзакции. Это необходимо, когда временно нарушается целостность в рамках одной транзакции (например, при переносе данных между связанными таблицами или рекурсивном обновлении иерархии). Объявление:

ALTER TABLE orders
    ADD CONSTRAINT fk_orders_user
    FOREIGN KEY (user_id) REFERENCES users(id)
    DEFERRABLE INITIALLY DEFERRED;

Теперь проверка fk_orders_user произойдёт только при COMMIT. Внутри транзакции допускаются временные нарушения.

Конструкция NOT VALID применяется при добавлении ограничения к существующей таблице с большими объёмами данных. Она говорит СУБД: «не проверяй старые строки сейчас, но требуй соблюдения правила для всех новых и изменённых»:

ALTER TABLE products
    ADD CONSTRAINT chk_price_positive CHECK (price > 0) NOT VALID;

После этого можно в фоновом режиме выполнить валидацию:

ALTER TABLE products VALIDATE CONSTRAINT chk_price_positive;

VALIDATE CONSTRAINT сканирует таблицу и проверяет только те строки, которые не были затронуты после добавления NOT VALID. Это позволяет избежать долгой блокировки таблицы при первоначальном применении ограничения.

Практика проектирования

Ограничения — мощный инструмент, но не универсальный. Их применение требует осознанного подхода.

Когда ограничений недостаточно
Существуют сценарии, выходящие за рамки возможностей CHECK:

правила, зависящие от агрегатов («в отделе не более 10 сотрудников»);
кросс-табличные проверки («общая сумма заказов клиента не должна превышать лимит в справочнике»);
временные условия с неточным окном («цена не может меняться чаще чем раз в сутки»).

В таких случаях используются:

Триггеры — императивный код, выполняемый при событии в таблице. Они гибки, но сложны в тестировании и могут снижать производительность.
Прикладная логика — проверки на стороне приложения. Этот подход централизует бизнес-правила, но не защищён от обхода через прямой доступ к БД.

Оптимальная стратегия — максимальное использование декларативных ограничений для фундаментальных правил (типы, ссылки, диапазоны), дополнение их триггерами для средней сложности и резервирование прикладной логики для высокоуровневых, изменчивых требований.

ON DELETE CASCADE — безопасность и риски
Это поведение безопасно, когда:

дочерние сущности не имеют самостоятельной ценности вне контекста родителя;
удаление родителя означает завершение всего жизненного цикла агрегата;
нет параллельных процессов, ссылающихся на дочерние данные (например, фоновых задач, кэшей).

Опасно, когда:

дочерние данные используются в аналитике, отчётах или аудите;
существует цепочка каскадов (A → B → C → D), где удаление одной строки может неожиданно уничтожить тысячи записей;
возможна рекурсивная зависимость (например, «сотрудник — руководитель»).

Перед включением CASCADE рекомендуется смоделировать цепочку удаления с помощью EXPLAIN (VERBOSE) или временного переключения на NO ACTION.

Внешний ключ без индекса — последствия
Отсутствие индекса приводит к:

замедлению DELETE и UPDATE в родительской таблице (сканирование дочерней);
росту времени выполнения INSERT в дочернюю таблицу при высокой конкуренции;
увеличению нагрузки на блокировки и лог транзакций.

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

Управление ограничениями (PostgreSQL)

PostgreSQL предоставляет развитый инструментарий для работы с ограничениями в эксплуатации.

Добавление после создания таблицы
Все типы ограничений можно добавить через ALTER TABLE … ADD CONSTRAINT. Для FOREIGN KEY требуется, чтобы целевой столбец в родительской таблице имел PRIMARY KEY или UNIQUE. Для CHECK — чтобы выражение было валидным для всех существующих строк (либо использовался NOT VALID).

Просмотр ограничений
Метаданные хранятся в системном каталоге pg_constraint:

SELECT
    conname AS constraint_name,
    contype AS type,
    condeferrable AS deferrable,
    convalidated AS validated,
    pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'orders'::regclass;

Поле contype кодирует тип: p — первичный ключ, u — уникальность, f — внешний ключ, c — проверка.

Отложенная проверка вручную
Даже для не-отложенных ограничений проверку можно отложить внутри транзакции:

BEGIN;
SET CONSTRAINTS ALL DEFERRED;
-- выполняем операции, временно нарушающие целостность
COMMIT; -- проверка происходит здесь

Это полезно при выполнении миграций, где порядок операций не совпадает с логикой ссылок.

Сравнение СУБД

Поддержка ограничений варьируется между системами.

PostgreSQL

Полная поддержка всех стандартных ограничений (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL);
Расширения: EXCLUDE, NULLS NOT DISTINCT (в UNIQUE и PRIMARY KEY, начиная с 15-й версии — позволяет считать NULL = NULL);
Гибкость: отложенные проверки, NOT VALID, VALIDATE CONSTRAINT;
CHECK запрещает недетерминированные функции и подзапросы.

В PostgreSQL допустимо NOT NULL col вне определения столбца, но такая форма не рекомендуется для применения в производственных DDL-скриптах — она используется утилитой pg_dump для восстановления метаданных и не переносима на другие СУБД. Функционально NOT NULL остаётся ограничением столбца: его логика привязана к одному атрибуту, и проверка происходит независимо для каждой строки и каждого поля.

Microsoft Access

Поддерживает PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL;
CHECK в DDL не поддерживается — только через интерфейс таблицы или макросы;
Внешние ключи могут объявляться с опцией NO INDEX, что приводит к неявному созданию невидимого индекса;
Отсутствуют отложенные проверки, NOT VALID, EXCLUDE.

В Microsoft Access допустимо объявление CONSTRAINT … NOT NULL (col1, col2).

В Microsoft Access для внешнего ключа доступен модификатор NO INDEX. Он указывает СУБД не создавать автоматический индекс по столбцам внешнего ключа. Такая опция применяется в особых случаях, когда распределение значений во внешнем ключе крайне неравномерное — например, при наличии доминирующего значения (NULL, 0, «неизвестно»), составляющего значительную долю строк. В подобных сценариях индекс становится неэффективным: его использование требует дополнительных операций чтения (из-за высокой селективности), а полное сканирование таблицы оказывается быстрее. Однако при частых вставках и удалениях использование NO INDEX приводит к ухудшению производительности операций в родительской таблице, поскольку каждая проверка ссылочной целостности требует полного сканирования дочерней таблицы.

Общие ограничения всех СУБД

В CHECK нельзя использовать подзапросы;
Недетерминированные функции (например, NOW(), RANDOM()) запрещены в CHECK в большинстве систем;
Ограничения не могут основываться на данных из других таблиц;
Поведение при NULL в UNIQUE стандартизировано: несколько NULL допускаются.

Антипримеры и диагностика

Как найти нарушения существующих ограничений
Если ограничение добавлено с NOT VALID, его можно проверить отдельно:

-- Найти строки, нарушающие chk_price_positive
SELECT * FROM products WHERE NOT (price > 0);

Для внешних ключей:

SELECT o.*
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE o.user_id IS NOT NULL AND u.id IS NULL;

Что делать, если данные уже некорректны

Оценить масштаб: сколько строк нарушает правило, какова их бизнес-значимость.
Принять решение:
- Исправить данные (например, установить корректный user_id);
- Удалить строки (если они мусорные);
- Оставить как есть и использовать NOT VALID, если нарушения допустимы в историческом контексте.
Зафиксировать решение в документации и, при необходимости, добавить миграцию.

Как избежать проблем при миграциях

Всегда тестируйте DDL-скрипты на копии продакшн-базы.
Используйте NOT VALID для добавления CHECK к большим таблицам.
Создавайте индексы до добавления FOREIGN KEY, иначе блокировка будет дольше.
Для составных внешних ключей убедитесь, что в родительской таблице есть уникальный индекс в том же порядке столбцов.
Избегайте изменения типа столбца первичного/внешнего ключа «на лету» — сначала удалите зависимости.

Ограничения в SQL​

Почему данные могут оказаться некорректными​

Классификация ограничений​

Первичный ключ (PRIMARY KEY)​

Внешний ключ (FOREIGN KEY)​

Уникальность (UNIQUE)​

Запрет пустых значений (NOT NULL)​

Проверка условия (CHECK)​

Значение по умолчанию (DEFAULT)​

Ограничения столбца и ограничения таблицы​

Именованные и анонимные ограничения​

EXCLUDE — ограничение пересечения (PostgreSQL)​

Взаимодействие с индексами​

Отложенная проверка, NOT VALID, VALIDATE CONSTRAINT​

Практика проектирования​

Управление ограничениями (PostgreSQL)​

Сравнение СУБД​

Антипримеры и диагностика​