修改EF Core Schema的onDelete属性无效，存在外键约束问题

问题背景 #

创建SeriesRelation表用于关联两个Series并存储元数据，需求为：删除Series时自动删除关联的SeriesRelation行，但删除SeriesRelation时不删除源Series。当前因FK_SeriesRelation_Series_SeriesId外键的删除规则为Restrict，导致删除关联了SeriesRelation的Series时触发外键约束报错。尝试自定义迁移修改该外键的级联规则未生效，修改Fluent API配置后问题依旧。

尝试过的自定义迁移代码 #

migrationBuilder.DropForeignKey(
                name: "FK_SeriesRelation_Series_SeriesId",
                table: "SeriesRelation");
            migrationBuilder.AddForeignKey(
                name: "FK_SeriesRelation_Series_SeriesId",
                table: "SeriesRelation",
                column: "SeriesId",
                principalTable: "Series",
                principalColumn: "Id",
                onDelete: ReferentialAction.Cascade);

原迁移代码 #

migrationBuilder.CreateTable(
                name: "SeriesRelation",
                columns: table => new
                {
                    Id = table.Column<int>(type: "INTEGER", nullable: false)
                        .Annotation("Sqlite:Autoincrement", true),
                    RelationKind = table.Column<int>(type: "INTEGER", nullable: false),
                    TargetSeriesId = table.Column<int>(type: "INTEGER", nullable: false),
                    SeriesId = table.Column<int>(type: "INTEGER", nullable: false)
                },
                constraints: table =>
                {
                    table.PrimaryKey("PK_SeriesRelation", x => x.Id);
                    table.ForeignKey(
                        name: "FK_SeriesRelation_Series_SeriesId",
                        column: x => x.SeriesId,
                        principalTable: "Series",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Restrict);
                    table.ForeignKey(
                        name: "FK_SeriesRelation_Series_TargetSeriesId",
                        column: x => x.TargetSeriesId,
                        principalTable: "Series",
                        principalColumn: "Id",
                        onDelete: ReferentialAction.Cascade);
                });

            migrationBuilder.CreateIndex(
                name: "IX_SeriesRelation_SeriesId",
                table: "SeriesRelation",
                column: "SeriesId");

            migrationBuilder.CreateIndex(
                name: "IX_SeriesRelation_TargetSeriesId",
                table: "SeriesRelation",
                column: "TargetSeriesId");

builder.Entity<SeriesRelation>()
                .HasOne(pt => pt.Series)
                .WithMany(p => p.Relations)
                .HasForeignKey(pt => pt.SeriesId)
                .OnDelete(DeleteBehavior.ClientCascade);

            builder.Entity<SeriesRelation>()
                .HasOne(pt => pt.TargetSeries)
                .WithMany(t => t.RelationOf)
                .HasForeignKey(pt => pt.TargetSeriesId);

编辑后的Fluent API配置（仍无效） #

builder.Entity<SeriesRelation>()
            .HasOne(pt => pt.Series)
            .WithMany(p => p.Relations)
            .HasForeignKey(pt => pt.SeriesId)
            .OnDelete(DeleteBehavior.ClientCascade);

        builder.Entity<SeriesRelation>()
            .HasOne(pt => pt.TargetSeries)
            .WithMany(t => t.RelationOf)
            .HasForeignKey(pt => pt.TargetSeriesId)
            .OnDelete(DeleteBehavior.Cascade); // 新增此规则但无效

解决思路 #

• 确认迁移执行状态

  1. 检查EF Core迁移历史表__EFMigrationsHistory，确认自定义迁移是否已成功应用。若未执行，运行Update-Database（Package Manager Console）或dotnet ef database update（CLI）手动触发。
  2. 验证SQLite连接字符串是否添加Foreign Keys=True参数，SQLite默认禁用外键约束，需显式开启才能让级联规则生效。

• 修正Fluent API配置
  当前SeriesId对应的删除行为是ClientCascade，仅在EF Core上下文内生效，不会同步到数据库外键约束。需改为DeleteBehavior.Cascade以生成数据库级的级联规则：

  builder.Entity<SeriesRelation>()
      .HasOne(pt => pt.Series)
      .WithMany(p => p.Relations)
      .HasForeignKey(pt => pt.SeriesId)
      .OnDelete(DeleteBehavior.Cascade);
  

  修改后重新生成迁移（Add-Migration UpdateSeriesRelationCascade或dotnet ef migrations add UpdateSeriesRelationCascade），再执行迁移更新数据库。

• 手动修改数据库（应急方案）
  若迁移始终不生效，可直接通过SQL语句修改SQLite数据库：

  -- 删除旧外键（若SQLite不支持直接删除，需通过临时表迁移数据替换原表）
  ALTER TABLE SeriesRelation DROP CONSTRAINT FK_SeriesRelation_Series_SeriesId;
  -- 添加带级联删除的新外键
  ALTER TABLE SeriesRelation ADD CONSTRAINT FK_SeriesRelation_Series_SeriesId FOREIGN KEY (SeriesId) REFERENCES Series(Id) ON DELETE CASCADE;
  

内容的提问来源于stack exchange，提问作者bittermelonman