date time comments 0 pageviews

最近在一個小系統看到 SQL Server 上有啟用串聯刪除 (Cascading Delete) 的 Foreign Key，我很少採用這種設計，當年學資料庫做完 Lab 便已淡忘，如今有緣看到野生版本，是老天爺的安排，寫篇筆記溫故知新。

關聯式資料庫有所謂 Primary Key (主鍵，簡稱 PK)、Foreign Key (外鍵，簡稱 FK) 的概念。PK 是一筆資料具唯一性識別資料，就像「身分證字號」或「學號」，一個資料表通常會設一個 PK；FK 用來建立兩張資料表之間的「關聯」，以標示資料的從屬關係。例如，文章的留言會有個欄位用來儲存這則留言屬於哪篇文章。FK 的一項重要使命是確保資料完整性，以留言的例子，定義 FK 後，若留言指向的文章不存在，試圖寫入留言資料時就會出現錯誤。

用一個簡單例子示範。我在 SQL 建了三個資料表，分別儲存文章(Posts)、留言(Comments)與使用者(Users)資料，三者的 PK 為 PostId、CommentId 及 UserId，另外有三個 FK 關聯，Comments.PostId 指向 Posts.PostId、Posts 及 Comments 都有 UserId 指向 Users.UserId：

隨意塞幾筆測試資料：

INSERT INTO Users VALUES ('alice', N'Alice');
INSERT INTO Users VALUES ('bob', N'Bob');
INSERT INTO Posts VALUES (1, 'alice', N'Greeting', N'Hi there.');
INSERT INTO Comments VALUES (1, 1, 'bob', N'Nice post, Alice!');

有了 FK 保護，我們就不能直接刪除有留言的文章(會讓留言找不到所屬文章)，也不能新增不存在作者的文章，資料庫會拋出錯誤阻止我們亂來。

DELETE FROM Posts WHERE PostId = 1;
--發生錯誤
Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK_Comments_PostId". The conflict occurred in database "Lab", table "dbo.Comments", column 'PostId'.

INSERT INTO Posts VALUES (2, 'jeffrey', N'Post from non-user', N'Integrity violation test.');
--發生錯誤
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Posts_UserId". The conflict occurred in database "Lab", table "dbo.Users", column 'UserId'.

以上操作的正確做法是先刪除文章的留言，再刪文章，以及先註使用者，再寫入該使用者的文章。

但 FK 除了限制跟檢核功能外，有些資料庫如 SQL Server 還支援所謂的串聯式動作(Cascading Action)，當刪除或修改資料時，一併更新跟它有 FK 關聯的資料。例如，當我刪除有留言的文章，讓 SQL 自動更新留言的 FK 欄位而不破壞關聯性，共有以下幾種動作選項：

  1. NO ACTION
    拋出錯誤，阻止刪除或更新
  2. CASCADE
    一併刪除關聯資料，或更新關聯資料的 FK 欄位值。例如：刪除 PostId = 1 文章時，將 Comments 資料表所有 PostId = 1 的留言一起刪除；或是將 PostId = 1 改成 2 時，將 Comments 資料表所有 PostId = 1 的留言的 PostId 換成 2
  3. SET NULL
    刪除資料或更新資料 PK 時，將其關聯資料的 FK 欄位設成 NULL
  4. SET DEFAULT
    刪除資料或更新資料 PK 時，將其關聯資料的 FK 欄位設成預設值

SSMS 的設定介面在這裡：

T-SQL 語法範例如下：參考

ALTER TABLE [Comments] ADD CONSTRAINT [FK_Comments_PostId] 
    FOREIGN KEY ([PostId]) REFERENCES [Posts]([PostId]) 
    ON DELETE CASCADE
    ON UPDATE CASCADE;
-- ON { UPDATE | DELETE } { NO ACTION | CASCADE | SET NULL | SET DEFAULT };

【同場加映】以下 SQL 查詢可用來盤點資料庫所有 FK 關聯：

SELECT 
    RC.CONSTRAINT_NAME AS ForeignKeyName,
    KCU1.TABLE_NAME + '.' + KCU1.COLUMN_NAME AS FKColName,
    KCU2.TABLE_NAME + '.' + KCU2.COLUMN_NAME AS RefColName,
    RC.DELETE_RULE AS OnDeleteRule,
    RC.UPDATE_RULE AS OnUpdateRule
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS RC
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU1
    ON RC.CONSTRAINT_NAME = KCU1.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU2
    ON RC.UNIQUE_CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME
    AND KCU1.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
ORDER BY FKColName, ForeignKeyName

設定 FK_Comments_PostId ON DELETE CASCADE ON UPDATE CASCADE，就能直接執行 UPDATE Posts SET PostId = 2 WHERE PostId = 1、DELETE FROM Posts WHERE PostId = 2，SQL 會自動更新或刪除 Comments。

不過，實務上並非所有資料都適合串聯式刪除或更新，它可能造成效能問題、導致資料意外遺失或事後稽查困難，例如：

  • 在數百萬筆資料表觸發串聯式刪除或更新可能導致資料庫鎖定 (Locking)，影響效能
  • 誤刪一筆資料導致整串交易紀錄憑空消失，且事後難以追查
  • 部分系統採 Soft Delete 策略，傾向用 IsDeleted = 'Y' 註記資料刪除而非真的刪除資料，方便事後稽查追溯

故在設計時應審慎評估資料性質再做決定。

最後，順便提一下 .NET EFCore。EF Core 也支援設定 FK 關聯及串聯式刪除及更新。

以下是個簡單範例：

using System;
using System.Collections.Generic;
using Microsoft.EntityFrameworkCore;

namespace _0129.Models;

// 使用 Scaffold 工具依 Schema 產生程式碼
// dotnet ef dbcontext scaffold "Server=(local)\SQLExpress;...連線字串..." Microsoft.EntityFrameworkCore.SqlServer 
// --table Users --table Posts --table Comments --output-dir Models --context BlogDbContext --force
public partial class User
{
    public string UserId { get; set; } = null!;
    public string Name { get; set; } = null!;
    public virtual ICollection<Comment> Comments { get; set; } = new List<Comment>();
    public virtual ICollection<Post> Posts { get; set; } = new List<Post>();
}
public partial class Post
{
    public int PostId { get; set; }
    public string UserId { get; set; } = null!;
    public string Title { get; set; } = null!;
    public string Body { get; set; } = null!;
    public virtual ICollection<Comment> Comments { get; set; } = new List<Comment>();
    public virtual User User { get; set; } = null!;
}
public partial class Comment
{
    public int CommentId { get; set; }
    public int PostId { get; set; }
    public string UserId { get; set; } = null!;
    public string Comment1 { get; set; } = null!;
    public virtual Post Post { get; set; } = null!;
    public virtual User User { get; set; } = null!;
}

public partial class BlogDbContext : DbContext
{
    public BlogDbContext() { }
    public BlogDbContext(DbContextOptions<BlogDbContext> options)
        : base(options) { }
    public virtual DbSet<Comment> Comments { get; set; }
    public virtual DbSet<Post> Posts { get; set; }
    public virtual DbSet<User> Users { get; set; }
    public bool EnableDebugLog { get; set; } = false;
    public void DebugLog(string msg)
    {
        if (!EnableDebugLog) return;
        Console.WriteLine(msg);
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer("Server=(local)\\SQLExpress;Database=Lab;Trusted_Connection=True;TrustServerCertificate=True;")
            .LogTo(DebugLog, Microsoft.Extensions.Logging.LogLevel.Information);

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Comment>(entity =>
        {
            entity.Property(e => e.CommentId).ValueGeneratedNever();
            entity.Property(e => e.Comment1).HasColumnName("Comment");
            entity.Property(e => e.UserId)
                .HasMaxLength(16)
                .IsUnicode(false);

            // 設定 Comment -> Post 關聯並啟用串聯刪除
            // 當 Post 被刪除時，相關的 Comment 也會被刪除
            entity.HasOne(d => d.Post).WithMany(p => p.Comments)
                .HasForeignKey(d => d.PostId)
                .OnDelete(DeleteBehavior.Cascade)  // 串聯刪除
                .HasConstraintName("FK_Comments_PostId");

            entity.HasOne(d => d.User).WithMany(p => p.Comments)
                .HasForeignKey(d => d.UserId)
                .HasConstraintName("FK_Comments_UserId");
        });

        modelBuilder.Entity<Post>(entity =>
        {
            entity.Property(e => e.PostId).ValueGeneratedNever();
            entity.Property(e => e.Title).HasMaxLength(256);
            entity.Property(e => e.UserId)
                .HasMaxLength(16).IsUnicode(false);

            entity.HasOne(d => d.User).WithMany(p => p.Posts)
                .HasForeignKey(d => d.UserId)
                .HasConstraintName("FK_Posts_UserId");
        });

        modelBuilder.Entity<User>(entity =>
        {
            entity.Property(e => e.UserId)
                .HasMaxLength(16)
                .IsUnicode(false);
            entity.Property(e => e.Name).HasMaxLength(32);
        });
        OnModelCreatingPartial(modelBuilder);
    }
    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

在 OnModelCreating 宣告 HasForeignKey() 可建立 FK 關聯、OnDelete(DeleteBehavior.Cascade) 則會觸發 EFCore 模型的串聯式刪除行為，而 HasConstraintName() 用來宣告 FK 名稱，方便 Migration 與 DB 端對應。

宣告 .OnDelete(DeleteBehavior.Cascade) 的意義有二：一是規範 EF Core 模型端的串聯式刪除行為，二是在 Migrate 時在資料庫建立 Foreign Key 並設定 ON DELETE CASCADE。

以下是個簡單測試：

using Microsoft.EntityFrameworkCore;
using _0129.Models;
using Microsoft.EntityFrameworkCore.Diagnostics;

namespace CascadeDemo;

class Program
{
    static async Task Main(string[] args)
    {
        using var context = new BlogDbContext();
        // 清除所有 Posts 及 Comments
        context.Comments.RemoveRange(context.Comments);
        context.Posts.RemoveRange(context.Posts);
        await context.SaveChangesAsync();
        // 塞入一筆 Post
        context.Posts.Add(new Post
        {
            PostId = 1,
            UserId = "alice",
            Title = "First post",
            Body = "Hello World!"
        });
        // 塞入一筆 Comment
        context.Comments.Add(new Comment
        {
            CommentId = 1,
            PostId = 1,
            UserId = "bob",
            Comment1 = "Nice post!"
        });
        await context.SaveChangesAsync();
        // 查詢 Foreign Key 是否有 CASCADE DELETE
        var updateRule = context.Database
            .SqlQueryRaw<string>($"SELECT DELETE_RULE AS Value FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE CONSTRAINT_NAME = 'FK_Comments_PostId'")
            .FirstOrDefault();
        Console.WriteLine($"FK_Comments_PostId DELETE_RULE: {updateRule}");
        // 嘗試刪除 Post
        var post = await context.Posts.FindAsync(1);
        context.Posts.Remove(post!);
        try
        {
            context.EnableDebugLog = true;
            await context.SaveChangesAsync();
            Console.WriteLine("Post 刪除成功");
        }
        catch (DbUpdateException ex)
        {
            Console.WriteLine("刪除失敗");
            Console.WriteLine($"錯誤訊息: {ex.InnerException?.Message ?? ex.Message}");
        }
    }

}

我先清空 Posts 與 Comments，接著插入一筆 Post 一筆 Comment，然後直接刪除 Post。刪除先檢查 FK_Comments_PostId 的 ON DELETE 設定。

在這個實驗中，由於我們插入 Post 跟 Comment 後隨即刪除剛插入的 Post，此時 Post 跟 Comment 都在 EF Core 追蹤範圍內，因此 EFCore 會產生兩個 DELETE 指令，先刪除 PostId = 1 的 Comment，再刪除 PostId = 1 的 Post；即便 FK_Comments_PostId 設定 ON DELETE CASCADE，EF Core 仍會在模型端自行刪除關聯項目，不依賴 DB 端的 Trigger 行為。

但客戶端的串聯刪除的前題是關聯的個體已被載入，且 EF Core 有在追蹤狀態。我們將 FK_Comments_PostId 設定 ON DELETE NO ACTION，並在 var post = await context.Posts.FindAsync(1); 前 context.ChangeTracker.Clear(); 取消對 PostId = 1 Comment 的追蹤，此時要刪除便只會有 DELETE FROM Posts WHERE PostId = 1 一個動作，並觸發 FK 關聯衝突：

因此，是在 EF Core 宣告 .OnDelete(DeleteBehavior.Cascade)，FK_Comments_PostId 也設定 ON DELETE CASCADE，是實作串聯式刪除較完善的做法。演練完畢!

Reviews SQL Server foreign keys and cascading deletes, explaining behaviors, risks, and use cases, with hands-on SQL examples and EF Core integration details, highlighting differences between database-level and ORM-level cascade delete behavior.


Comments

Be the first to post a comment

Post a comment