Can a cascade delete rule be added in a migration?

Question

In a FluentMigrator migration, if I'm defining a relationship, say:

Create.Table("RelatedTable")
    .WithColumn("RelatedTableId").AsGuid().PrimaryKey()
    .WithColumn("MainTableId").AsGuid().NotNullable();
    .WithColumn("SomeInfo").AsString().NotNullable();

Create.Table("MainTable")
    .WithColumn("MainTableId").AsGuid().PrimaryKey()
        .ReferencedBy("FK_RelatedTable_RelatedTableId", "RelatedTable", "MainTableId")
    .WithColumn("AField").AsInt64().NotNullable()
    .WithColumn("AnotherField").AsString().NotNullable();

Is there any way to define cascading delete type of relationship between them? Eg, if you delete something from MainTable, any related records are also deleted?

Vast

Answer 1

You can create a separate foreign key in the same migration like this, with the option of setting your cascading rules:

Create.ForeignKey("FK_RelatedTable_RelatedTableId")
               .FromTable("RelatedTable").ForeignColumn("RelatedTableId")
               .ToTable("MainTable").PrimaryColumn("MainTableId")
               .OnDeleteOrUpdate(System.Data.Rule.Cascade);

Hope this helps.

Persis

Note that, at least in the latest version, it is also possible to define them in the table defintion: ForeignKey("abc", "dfg").OnDeleteOrUpdate(System.Data.Rule.Cascade) – Gushy

Answer 2

The best I've been able to do on short notice is to execute the SQL to create the relationship myself. I created an extension method that does this and adds a cascade delete, as this is the only option I need for this project:

public static void AddCascadeDeleteRelationship(
    this Migration db,
    String primaryTable,
    String primaryField,
    String foreignTable,
    String foreignField,
    String relationshipName)
{
    db.Execute.Sql(
        String.Format(
            "ALTER TABLE [{0}] ADD CONSTRAINT {1} FOREIGN KEY ( [{2}] ) " +
                "REFERENCES [{3}] ( [{4}] ) ON DELETE CASCADE;",
            foreignTable, relationshipName, foreignField, primaryTable, primaryField)
    );
}

Is there a better way?

Vast