Cascade FK migrations on large referenced tables

Pattern for changing a foreign key's ON DELETE action on a high-traffic clinical table without taking a long write lock during the deploy.

When to useCopy Anchor Link

A migration that swaps NO ACTION / RESTRICT for ON DELETE CASCADE (or vice versa) on a column that references a busy table. The naive change_column path holds ACCESS EXCLUSIVE on the referencing table and SHARE ROW EXCLUSIVE on the referenced one while it scans every row of the referencing table to validate the constraint. On a table with millions of rows this blocks writes for minutes.

ShapeCopy Anchor Link

backend/db/migrate/<ts>_<verb>_<table>_<column>.rb

The migration must:

1. Call disable_ddl_transaction! so each FK validates in its own statement and commits before the next one begins.
2. Inside an inner ActiveRecord::Base.transaction, DROP CONSTRAINT IF EXISTS then ADD CONSTRAINT … NOT VALID — both statements scan neither table, so the inner transaction only holds ACCESS EXCLUSIVE briefly and there is no window where the column is FK-less.
3. Outside the inner transaction, ALTER TABLE … VALIDATE CONSTRAINT … — this takes only SHARE UPDATE EXCLUSIVE, so production writes to the referenced table continue.

class CascadeAiSuggestionLinksOnResourceDelete < ActiveRecord::Migration[8.1]
  disable_ddl_transaction!

  def up
    RESOURCE_FKS.each do |constraint, (table, column)|
      recreate_fk(constraint, table, column, on_delete: 'ON DELETE CASCADE')
    end
  end

  private

  def recreate_fk(constraint, table, column, on_delete:)
    ActiveRecord::Base.transaction do
      execute "ALTER TABLE clinical.ai_suggestion_links DROP CONSTRAINT IF EXISTS #{constraint}"
      execute <<~SQL.squish
        ALTER TABLE clinical.ai_suggestion_links
          ADD CONSTRAINT #{constraint}
          FOREIGN KEY (tenant_id, #{column})
          REFERENCES #{table}(tenant_id, id) #{on_delete} NOT VALID
      SQL
    end

    execute "ALTER TABLE clinical.ai_suggestion_links VALIDATE CONSTRAINT #{constraint}"
  end
end

ConstraintsCopy Anchor Link

• disable_ddl_transaction! means a mid-deploy failure leaves the schema in a partial state. Each FK in the loop is independent, so the failure is contained to that one FK.
• If the referencing table can carry rows that already violate the new constraint (e.g. orphan rows from a previous broken state), VALIDATE CONSTRAINT rejects them and aborts the migration. Pre-deploy, DELETE orphans whose target row is gone — they are by definition meaningless (the linked resource does not exist) and the migration's header comment should call out the policy.

TestingCopy Anchor Link

A dedicated migration test in backend/domains/<domain>/test/migrations/<migration_name>_test.rb runs migration.up and migration.down directly inside the per-test transaction (plain DDL is transactional in PostgreSQL and rolls back with the test, so the real constraint definitions are exercised without touching migration state). The test should assert:

• Each constraint name has the expected ON DELETE action after up.
• down removes the action.
• up → down → up is idempotent.
• The cascade actually fires: create a parent row + link, delete the parent, assert the link row is gone and the original suggestion row is untouched.

See backend/domains/clinical/test/migrations/cascade_ai_suggestion_links_on_resource_delete_test.rb for the worked example.