knex/knex · error · Error

Refusing to create transaction: unable to change `foreign_ke

Error message

Refusing to create transaction: unable to change `foreign_keys` pragma inside a nested transaction

What it means

SQLite ignores PRAGMA foreign_keys statements issued inside a running transaction (see sqlite.org/pragma.html#pragma_foreign_keys). Knex's strict SQLite transaction mode detects when it is nested inside an outer transaction AND the pragma actually needs to change (restoreForeignCheck is non-null) AND strict mode is on, and refuses to proceed because the requested enforcement cannot be silently guaranteed. The error is thrown before BEGIN so the connection is left clean.

Source

Thrown at lib/dialects/sqlite3/execution/sqlite-transaction.js:93

        // can leave the connection in an unexpected state. Just reject the begin transaction.
        const error = new Error(
          `Refusing to create transaction: failed to set \`foreign_keys\` pragma to the required value of ${enforceForeignCheck}`
        );
        error.cause = e;
        throw error;
      }

      // if:
      // - we're in a nested transaction
      // - _and_ we're in strict mode
      // - _and_ we are required to change the pragma
      // then: we cannot continue, it's out of our hands
      if (
        strictForeignKeyPragma &&
        hasOuterTransaction &&
        restoreForeignCheck != null
      ) {
        throw new Error(
          `Refusing to create transaction: unable to change \`foreign_keys\` pragma inside a nested transaction`
        );
      }

      let maybeWrappedContainer = container;
      if (restoreForeignCheck === true) {
        // in the case where we are turning foreign key checks off for the duration of a transaction,
        // we need to assert that there are no violations once the work of the transaction has been
        // completed. this relies on the fact that Transaction._onAcquire runs the "container" promise
        // to completion before executing "COMMIT"
        maybeWrappedContainer = async (trx) => {
          const res = await container(trx);

          const foreignViolations = await this.client
            .raw(executeForeignCheck())
            .connection(conn);

          if (foreignViolations.length > 0) {

View on GitHub (pinned to e25d54bcb7)

Solutions

Run schema/DDL operations outside of an outer transaction, or use savepoint-free top-level transactions for DDL.
If you must nest, ensure the pragma is already in the desired state before the outer transaction begins so the inner strict transaction detects no change is needed (restoreForeignCheck stays null).
Set PRAGMA foreign_keys to the target value once at connection initialization so no in-transaction change is required.

Example fix

// before
await knex.transaction(async (trx) => {
  await knex.schema.withUserParams({}).alterTable('t', (b) => b.setNullable('c'));
});
// after
await knex.schema.alterTable('t', (b) => b.setNullable('c'));

Defensive patterns

Strategy: validation

Validate before calling

// detect nested-transaction + strict + pragma-change risk before opening
async function currentFkEnabled(knex) {
  const r = await knex.raw('pragma foreign_keys');
  return r[0].foreign_keys === 1;
}
const alreadyTransacting = !!knex.transacting;
const needChange = (await currentFkEnabled(knex)) !== desiredEnforce;
if (alreadyTransacting && knex.strictForeignKeyPragma && needChange) {
  throw new Error('Cannot change foreign_keys pragma inside a nested transaction; run DDL at the top level.');
}

Try / catch

try {
  await knex.schema.alterTable('t', (b) => b.setNullable('c'));
} catch (e) {
  if (/nested transaction/i.test(e.message)) {
    // move the DDL call outside the outer transaction
  } else throw e;
}

Prevention

Run schema DDL outside of explicit transactions.
Set PRAGMA foreign_keys once at pool init so in-transaction changes are never required.
Avoid wrapping migrations in a single outer transaction.

When it happens

Trigger: A schema-DDL operation (which runs on the strict client) is invoked while already inside an outer knex.transaction(), and the connection's current foreign_keys pragma differs from what the inner transaction requires. For example: opening knex.transaction() then inside it calling a schema builder that triggers client.ddl() (strict) which itself tries to set enforceForeignCheck.

Common situations: Running migrations or schema-alter calls inside an explicit outer transaction. Wrapping schema.alterTable / setNullable / dropColumn in a user-managed transaction. Pooling reuse where a prior DDL op left foreign_keys in a different state than the outer transaction expects.

Related errors

AI-assisted analysis of knex/knex@e25d54bcb7 (2026-08-03). Data as JSON: /data/errors/2e24d93916e07db0.json. Report an issue: GitHub.