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 и без отключения ограничений.

Не путать с отключением FK

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

---

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

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

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

CREATE TABLE teams (
    id      SERIAL PRIMARY KEY,
    name    TEXT NOT NULL,
    lead_id INT
);

CREATE TABLE employees (
    id      SERIAL PRIMARY KEY,
    name    TEXT NOT NULL,
    team_id INT
);

ALTER TABLE teams
    ADD CONSTRAINT teams_lead_id_fkey
    FOREIGN KEY (lead_id) REFERENCES employees(id)
    DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE employees
    ADD CONSTRAINT employees_team_id_fkey
    FOREIGN KEY (team_id) REFERENCES teams(id)
    DEFERRABLE INITIALLY DEFERRED;

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

---

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

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

await using var conn = new NpgsqlConnection(connectionString);
await conn.OpenAsync();

await using var tx = await conn.BeginTransactionAsync();

await using (var cmd = new NpgsqlCommand(
    "INSERT INTO teams (id, name, lead_id) VALUES (1, 'Backend', 42)", conn, tx))
{
    await cmd.ExecuteNonQueryAsync();
}

await using (var cmd = new NpgsqlCommand(
    "INSERT INTO employees (id, name, team_id) VALUES (42, 'Иван', 1)", conn, tx))
{
    await cmd.ExecuteNonQueryAsync();
}

await tx.CommitAsync();

Подробнее про ADO.NET и драйвер Npgsql — ADO.NET и драйверы, обзор PostgreSQL из .NET — 888.

---

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

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

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

protected override void Up(MigrationBuilder migrationBuilder)
{
    migrationBuilder.Sql(@"
        ALTER TABLE teams
        DROP CONSTRAINT IF EXISTS teams_lead_id_fkey;

        ALTER TABLE teams
        ADD CONSTRAINT teams_lead_id_fkey
        FOREIGN KEY (lead_id) REFERENCES employees(id)
        DEFERRABLE INITIALLY DEFERRED;
    ");
}

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

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

---

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).

---

См. также

• Ограничения целостности в SQL — NOT VALID, VALIDATE CONSTRAINT, каталог pg_constraint.
• Транзакции в SQL — BEGIN, COMMIT, изоляция.
• Практикум PostgreSQL 8.11 — эксплуатация и production, не прикладные FK.

---