‼️ --allow-destructive drops EVERY foreign key on an already-migrated DB (unnamed YAML FKs; also root cause of #79)

[MelbourneDeveloper]

‼️ --allow-destructive drops every foreign key on an already-migrated database

Versions: DataProviderMigrate 0.9.11-beta and 0.9.12-beta, Postgres (supabase/postgres:15.1.0.117).

Repro

1. Take a fresh database and a schema.yaml whose foreignKeys have no name: (the documented shape). Run migrate --allow-destructive. The 25 FKs are created.
2. Run the same command with the same unchanged schema again.
3. The output shows 25 × DropForeignKeyOperation, and pg_constraint now has 0 FKs in public.
4. Every later run exits 1 with SCHEMA INTEGRITY CHECK FAILED ... missing foreign key FK_<table>_<col>, because the FKs are never re-added (migrate does not ADD a foreign key to a pre-existing table (only creates FKs at table-creation time) #79).

An additive run (no --allow-destructive) leaves the FKs alone.

Root cause

SchemaDiff.CalculateForeignKeyDiff keys both sides on fk.Name, filtering desired.ForeignKeys.Where(fk => fk.Name is not null). YAML FKs are unnamed, so the desired set is empty:

• Add side: desiredFk.Name is not null && ... is never true, so an FK is never added to a pre-existing table. This is the root cause of migrate does not ADD a foreign key to a pre-existing table (only creates FKs at table-creation time) #79.
• Drop side: every introspected (named) FK is "not desired", so all of them are dropped.

PostgresDdlGenerator (fk.Name ?? $"FK_{table}_{cols}") and SchemaIntegrityVerifier both already default the name. The diff doesn't.

Fix

Normalise the FK name in the diff with the same default, FK_{table}_{string.Join("_", Columns)}, before building both dictionaries. Add a test: migrate, then migrate destructive again with the same schema, and expect zero operations and all FKs intact. Ideally also diff onDelete (#67).

Impact

Any consumer that runs the destructive phase of a two-phase migration loses referential integrity silently, including every ON DELETE CASCADE/SET NULL. NAP is holding all destructive migrates until this ships.