SQLite table reconstruction cascade-deletes child rows inside transactional migrations

Steps to reproduce

This is my first time working with SQLite, so I may be missing something obvious about the expected migration setup or behavior.

This reproduces on Active Record 8.1.3.1 and on Rails main at bd885fa.

# frozen_string_literal: true

require "bundler/inline"

gemfile(true) do
  source "https://rubygems.org"
  gem "activerecord", "8.1.3.1"
  gem "sqlite3", "~> 2.1"
  gem "minitest"
end

require "active_record"
require "minitest/autorun"
require "tmpdir"

class SQLiteCascadeMigrationTest < Minitest::Test
  def test_table_rebuild_inside_a_normal_migration_preserves_cascade_children
    Dir.mktmpdir do |directory|
      ActiveRecord::Base.establish_connection(
        adapter: "sqlite3",
        database: File.join(directory, "test.sqlite3")
      )
      connection = ActiveRecord::Base.connection

      connection.create_table(:authors) { |table| table.string :name, null: false }
      connection.add_check_constraint(
        :authors,
        "length(name) > 0",
        name: "authors_name_present"
      )
      connection.create_table(:books) do |table|
        table.references :author, null: false, foreign_key: { on_delete: :cascade }
      end
      connection.execute("INSERT INTO authors (id, name) VALUES (1, 'Douglas Adams')")
      connection.execute("INSERT INTO books (id, author_id) VALUES (1, 1)")

      migration_directory = File.join(directory, "migrate")
      Dir.mkdir(migration_directory)
      File.write(
        File.join(migration_directory, "20260909000000_remove_author_name_check.rb"),
        <<~RUBY
          class RemoveAuthorNameCheck < ActiveRecord::Migration[8.1]
            def change
              remove_check_constraint :authors, name: "authors_name_present"
            end
          end
        RUBY
      )

      migration_context = ActiveRecord::MigrationContext.new(
        migration_directory,
        ActiveRecord::Base.connection_pool.schema_migration,
        ActiveRecord::Base.connection_pool.internal_metadata
      )
      migration_context.migrate

      assert_equal 1, connection.select_value("SELECT COUNT(*) FROM authors")
      assert_equal 1, connection.select_value("SELECT COUNT(*) FROM books"),
        "the migration must not cascade-delete books while rebuilding authors"
    ensure
      ActiveRecord::Base.connection_pool.disconnect!
    end
  end
end

Expected behavior

Removing the check constraint rebuilds the authors table without deleting records from the referencing books table. Both assertions pass.

Actual behavior

The authors row survives, but the books row is deleted:

the migration must not cascade-delete books while rebuilding authors.
Expected: 1
  Actual: 0

This appears related to #55866 and #55907. That fix moved disable_referential_integrity outside the SQLite adapter's internal reconstruction transaction. However, the migration runner has already opened an outer transaction because SQLite reports support for transactional DDL. SQLite treats PRAGMA foreign_keys = OFF as a no-op while that transaction is active, so dropping the original parent table still fires the cascade.

The regression tests added by #55907 call adapter methods directly rather than running the operation through ActiveRecord::MigrationContext, so they do not exercise the outer migration transaction. Adding disable_ddl_transaction! to the generated migration makes this reproduction pass, but I could not find that requirement documented for ordinary SQLite schema operations.

System configuration

Rails version: Active Record 8.1.3.1; also reproduced on Rails main at bd885fa

Ruby version: 4.0.6

sqlite3 gem version: 2.9.6

SQLite version: 3.53.2