Repository: ariga/atlas
Pull request #1609 (2023-04-27 12:40:12)

sql/postgres: drop foreign-keys before dropping tables
Fixed https://github.com/ariga/atlas/issues/1585


Patch:
diff --git a/sql/internal/sqlx/plan.go b/sql/internal/sqlx/plan.go
index b3d7f9a2..33f9038f 100644
--- a/sql/internal/sqlx/plan.go
+++ b/sql/internal/sqlx/plan.go
@@ -252,6 +252,13 @@ func dependencies(changes []schema.Change) (map[string][]*schema.Table, error) {
 					if c.To.RefTable != change.T {
 						deps[change.T.Name] = append(deps[change.T.Name], c.To.RefTable)
 					}
+				case *schema.DropForeignKey:
+					if err := checkFK(c.F); err != nil {
+						return nil, err
+					}
+					if isDropped(changes, c.F.RefTable) {
+						deps[c.F.RefTable.Name] = append(deps[c.F.RefTable.Name], c.F.Table)
+					}
 				}
 			}
 		}


Test patch:
diff --git a/sql/internal/sqlx/plan_test.go b/sql/internal/sqlx/plan_test.go
index 7976b97f..3a198334 100644
--- a/sql/internal/sqlx/plan_test.go
+++ b/sql/internal/sqlx/plan_test.go
@@ -91,6 +91,20 @@ func TestDetachCycles(t *testing.T) {
 	users.ForeignKeys = nil
 	workplaces.ForeignKeys = nil
 	require.Equal(t, deletion, planned[2:])
+
+	// Delete associated table and foreign-key.
+	users.AddForeignKeys(
+		schema.NewForeignKey("workplace").AddColumns(users.Columns[1:2]...).SetRefTable(workplaces).AddRefColumns(workplaces.Columns[:1]...),
+	)
+	changes = []schema.Change{&schema.DropTable{T: workplaces}, &schema.ModifyTable{
+		T: users,
+		Changes: []schema.Change{
+			&schema.DropForeignKey{F: users.ForeignKeys[0]},
+		},
+	}}
+	planned, err = DetachCycles(changes)
+	require.NoError(t, err)
+	require.Equal(t, []schema.Change{changes[1], changes[0]}, planned)
 }
 
 func TestCheckChangesScope(t *testing.T) {
diff --git a/sql/postgres/migrate_test.go b/sql/postgres/migrate_test.go
index 9ce97daa..3277b3ca 100644
--- a/sql/postgres/migrate_test.go
+++ b/sql/postgres/migrate_test.go
@@ -1413,6 +1413,36 @@ func TestPlanChanges(t *testing.T) {
 				},
 			},
 		},
+		// Foreign keys should be dropped before the tables they reference.
+		{
+			changes: func() []schema.Change {
+				usersT := schema.NewTable("users").SetSchema(schema.New("public")).AddColumns(schema.NewIntColumn("id", "int"))
+				postsT := schema.NewTable("posts").SetSchema(schema.New("public")).
+					AddColumns(schema.NewIntColumn("id", "int"), schema.NewIntColumn("author_id", "int"))
+				postsT.AddForeignKeys(schema.NewForeignKey("author").AddColumns(postsT.Columns[1]).SetRefTable(usersT).AddRefColumns(usersT.Columns...))
+				return []schema.Change{
+					&schema.DropTable{T: usersT},
+					&schema.ModifyTable{T: postsT, Changes: []schema.Change{&schema.DropForeignKey{F: postsT.ForeignKeys[0]}}},
+				}
+			}(),
+			options: []migrate.PlanOption{
+				func(o *migrate.PlanOptions) { o.SchemaQualifier = new(string) },
+			},
+			wantPlan: &migrate.Plan{
+				Reversible:    true,
+				Transactional: true,
+				Changes: []*migrate.Change{
+					{
+						Cmd:     `ALTER TABLE "posts" DROP CONSTRAINT "author"`,
+						Reverse: `ALTER TABLE "posts" ADD CONSTRAINT "author" FOREIGN KEY ("author_id") REFERENCES "users" ("id")`,
+					},
+					{
+						Cmd:     `DROP TABLE "users"`,
+						Reverse: `CREATE TABLE "users" ("id" integer NOT NULL)`,
+					},
+				},
+			},
+		},
 		// Empty qualifier in multi-schema mode should fail.
 		{
 			changes: []schema.Change{