Библиотека шарписта | C#, F#, .NET, ASP.NET: “👩‍💻 Когда ограничения мешают вставке Иногда при работе с PostgreSQL…”

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

Иногда при работе с PostgreSQL нужно вставить несколько связанных записей в одной транзакции, но внешние ключи проверяются сразу и транзакция падает. DEFERRABLE INITIALLY DEFERRED решает эту проблему — ограничение откладывается до конца транзакции.

Что это и зачем

По умолчанию PostgreSQL проверяет ограничения (FOREIGN KEY, UNIQUE, CHECK) сразу при выполнении каждого оператора. Если порядок вставки нарушает ссылочную целостность — получаем ошибку, даже если к концу транзакции всё было бы корректно.

DEFERRABLE INITIALLY DEFERRED говорит базе: не проверяй ограничение после каждой операции — подожди до COMMIT.

Это удобно, когда:
- записи ссылаются друг на друга (циклические FK)
- порядок вставки нельзя гарантировать
- нужно пакетно импортировать данные без временного отключения ограничений

Пример схемы

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

CREATE TABLE teams (
    id      SERIAL PRIMARY KEY,
    name    TEXT NOT NULL,
    lead_id INT REFERENCES employees(id) DEFERRABLE INITIALLY DEFERRED
);

CREATE TABLE employees (
    id      SERIAL PRIMARY KEY,
    name    TEXT NOT NULL,
    team_id INT REFERENCES teams(id) DEFERRABLE INITIALLY DEFERRED
);

Без DEFERRABLE INITIALLY DEFERRED вставить команду со ссылкой на сотрудника, которого ещё нет — невозможно. С ним — всё проверяется один раз при COMMIT.

Как использовать в C# с Npgsql

Npgsql поддерживает транзакции напрямую. Главное — убедиться, что обе вставки выполняются в одной транзакции.

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();
}

// FK проверяются здесь — оба существуют, всё ок
await tx.CommitAsync();

Без DEFERRABLE INITIALLY DEFERRED первая вставка упала бы с ошибкой foreign key violation, потому что employees(42) не существует в момент вставки команды.

Как использовать с EF Core

EF Core сам по себе не умеет объявлять DEFERRABLE ограничения через Fluent API. Но можно сделать через HasCheckConstraint с raw SQL или через миграцию.

Самый надёжный способ — написать миграцию вручную:

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;
    ");
}

После этого транзакции EF Core будут пользоваться отложенной проверкой автоматически — ничего дополнительно настраивать не нужно.

Разница между DEFERRED и IMMEDIATE

DEFERRABLE означает, что ограничение можно отложить. INITIALLY DEFERRED — что оно отложено по умолчанию для каждой транзакции.

Если нужно временно изменить поведение внутри конкретной транзакции — можно явно переключить режим:

-- Внутри транзакции включить немедленную проверку
SET CONSTRAINTS teams_lead_id_fkey IMMEDIATE;

-- Или отложить конкретное ограничение
SET CONSTRAINTS teams_lead_id_fkey DEFERRED;

В C# через Npgsql это выполняется как обычный SQL-запрос внутри транзакции:

await using var cmd = new NpgsqlCommand(
    "SET CONSTRAINTS ALL DEFERRED", conn, tx);
await cmd.ExecuteNonQueryAsync();

📍 Навигация: Вакансии • Задачи • Собесы

🐸 Библиотека шарписта

#il_люминатор