Перейти к основному содержимому

PostgreSQL: отложенные ограничения и .NET

ОБЯЗАТЕЛЬНО
Разработчику Инженеру

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

Когда ограничения мешают вставке​

По умолчанию PostgreSQL проверяет FOREIGN KEY, UNIQUE и CHECK сразу после каждого оператора в транзакции. Если порядок INSERT временно нарушает ссылочную целостность, получаем foreign key violation, хотя к COMMIT данные уже были бы согласованы.

DEFERRABLE INITIALLY DEFERRED переносит проверку на конец транзакции (COMMIT или ROLLBACK).

Удобно, когда:

  • строки ссылаются друг на друга (циклические FK);
  • порядок вставки при импорте нельзя гарантировать;
  • нужен пакетный импорт без SET session_replication_role и без отключения ограничений (теория chunk/bulk/watermark — Пакетная работа с данными).
Не путать с отключением FK

Отложенная проверка работает внутри одной транзакции и не отменяет правило: при COMMIT нарушение по-прежнему приведёт к ошибке. Это не "выключить ограничения навсегда".

Схема с взаимными ссылками​

Пример: команда ссылается на руководителя-сотрудника, сотрудник — на команду.

Таблицы сначала создаём без внешних ключей (иначе CREATE TABLE teams упадёт: таблицы employees ещё нет). Ограничения добавляем через ALTER TABLE:

Код ITЗагрузка примера кода…

Без DEFERRABLE INITIALLY DEFERRED вставка команды с lead_id = 42 до появления employees(42) в той же транзакции завершится ошибкой. С отложенной проверкой оба INSERT допустимы до COMMIT.

Npgsql: одна транзакция​

Npgsql не требует отдельных настроек: главное — обе вставки в одной транзакции. Проверка FK сработает на CommitAsync.

Код ITЗагрузка примера кода…

Подробнее про ADO.NET и драйвер Npgsql — ADO.NET и драйверы, обзор PostgreSQL из .NET — PostgreSQL — практическая работа и API.

EF Core: только через миграцию​

Fluent API не объявляет DEFERRABLE для внешних ключей. Сгенерированная миграция создаст обычный FOREIGN KEY с немедленной проверкой.

Надёжный способ — правка Up вручную (после первой генерации или вместо автоматического AddForeignKey):

Код ITЗагрузка примера кода…

Аналогично — для обратной ссылки employees.team_id. После применения миграции SaveChanges внутри IDbContextTransaction использует отложенную проверку без дополнительного кода в приложении.

Общие правила миграций — Миграции баз данных. Первая программа на EF Core — Entity Framework Core — первая программа.

DEFERRED и IMMEDIATE​

ФразаСмысл
DEFERRABLEОграничение можно отложить в рамках транзакции
INITIALLY DEFERREDПо умолчанию в каждой транзакции проверка в конце
INITIALLY IMMEDIATEПо умолчанию сразу; отложить можно командой SET CONSTRAINTS

Внутри транзакции режим можно переключить:

SET CONSTRAINTS teams_lead_id_fkey IMMEDIATE;
SET CONSTRAINTS teams_lead_id_fkey DEFERRED;
SET CONSTRAINTS ALL DEFERRED;

Из C# — обычный NpgsqlCommand с тем же NpgsqlTransaction.

RESTRICT vs NO ACTION

Ограничение с ON DELETE RESTRICT нельзя отложить даже через SET CONSTRAINTS ALL DEFERRED. Подробнее — раздел про NO ACTION и RESTRICT в 444.

Что запомнить​

  • Отложенные FK решают порядок вставки в одной транзакции, а не отсутствие целостности.
  • Для циклических ссылок в DDL сначала таблицы, потом ALTER TABLE … ADD CONSTRAINT … DEFERRABLE.
  • В EF Core атрибут DEFERRABLE не задаётся — только SQL в миграции.
  • Альтернатива без смены схемы: в транзакции SET CONSTRAINTS ALL DEFERRED (если ограничения уже DEFERRABLE).

См. также​

Содержание