'Can a cascade delete rule be added in a migration?

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?

c++sharpfluent-migrator


Solution 1:[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.

Solution 2:[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?

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 lance
Solution 2 Remi Despres-Smyth

Related Questions