SQL Server Multiple Cascade Paths カスケード削除できない問題

問題について

SQL Serverを使う時に出会った問題ですが、仮にテーブルAとテーブルBがあり、Bでは2つの外部キーがあり、いずれもAをレファレンスしています。

それで、テーブルA（親）のレコードを削除するときに、テーブルBにおける全ての関連レコードも一緒に削除したい、いわばカスケード削除にしようと。

SQLにすると：

CREATE TABLE [dbo].[table_a](
 [id] [int] NOT NULL PRIMARY KEY,
 [name] [varchar] NOT NULL,
)

-- 
CREATE TABLE [dbo].[table_b](
 [id] [int] NOT NULL PRIMARY KEY,
 [fk1] [int] NULL FOREIGN KEY REFERENCES table_a(id) ON DELETE CASCADE,
 [fk2] [int] NULL FOREIGN KEY REFERENCES table_a(id) ON DELETE CASCADE,
)

これでマイグレーションしてみると、次のようなエラーが出てきます。

Introducing FOREIGN KEY constraint 'tabel_b_fk2_foreign' on table 'table_b' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

要するに、カスケード削除のカラムを2つ以上設定すると、複数のルート経由で同じテーブルのレコードを削除できるからいけないですよ、とのことです。

で、これはカスケードとON DELETEだけでなく、SET NULL、SET DEFAULTとON UPDATEも全部ダメで、とにかくデフォルトのNO ACTIONしか許されません。デフォルト設定だと、table_aのレコードを削除する時に、table_bに関連レコードがあれば、整合性を守るために削除ができない問題があります。

トリガー

何これ？？It's not a BUG, it's a feature!とでも？

と正直の感想でした。

確かにPostgreSQLとMySQLにはなかった問題です。複数のカスケードパスとサイクルを防止するための、SQL Serverが取ったポリシーだと思われますが、逆に不便も感じています。

MSの公式の説明はこちらとなります。

色々と調べて、DBの設計を変えず、DBを変えずにカスケードを実装する代案は一応ありました。トリガーです（詳しい例はこちら）。

SQL Serverのドキュメントが読みにくいのでここはPostgreSQLの説明を載せます：

A trigger is a specification that the database should automatically execute a particular function whenever a certain type of operation is performed. Triggers can be attached to tables (partitioned or not), views, and foreign tables.
(トリガーとは、特定の操作が行われる時にデーターベースが自動で実行すべき指定の関数のことです。トリガーはテーブル（分割かどうかに関わらず）、ビューと外部テーブルに添付することが可能です。
On tables and foreign tables, triggers can be defined to execute either before or after any INSERT, UPDATE, or DELETE operation, either once per modified row, or once per SQL statement...
（テーブルと外部テーブルでは、トリガーがINSERT, UPDATE, DELETE操作の前または後に、行ごとにまたはステートメントごとに実行するように定義できます。）
On views, triggers can be defined to execute instead of INSERT, UPDATE, or DELETE operations. Such INSTEAD OF triggers are fired once for each row that needs to be modified in the view...
（ビューの場合、トリガーがINSERT, UPDATE, DELETE操作の前または後に実行するように定義できます。このような「INSTEAD OF」トリガーが変更必要なレコード・行ごとに実行されます。）

もっと簡潔にいえば、DML操作・イベントの前後のタイミングに実行する関数、もしくはその操作自身を別のものに入れ替える関数、と言えるでしょう。

ただ注意しないといけないのは、SQL Serverのイベントとタイミングの定義が若干違います。特に、beforeというタイミングがありません。定義は以下となります。

CREATE TRIGGER [schema_name.]trigger_name
ON table_name
{FOR | AFTER | INSTEAD OF} {[INSERT] [,] [UPDATE] [,] [DELETE]}
AS
{sql_statements}

理由として、instead ofは実質beforeのタイミングに実行されるので、そちらが代案となるらしい（こちらのINSTEAD OF triggerに参照）。

解決案

INSTEAD OFトリガーがDML操作の前に実行されるため、タイミング的に、外部キー制約などのチェックなどもされる前となります。これを導入すると上記の問題が解決できます：

CREATE TRIGGER delete_a_record
  ON dbo.[table_a]
  INSTEAD OF DELETE
AS 
BEGIN
  SET NOCOUNT ON;
  DELETE FROM [table_b] WHERE fk1 IN (SELECT id FROM DELETED) 
  DELETE FROM [table_b] WHERE fk2 IN (SELECT id FROM DELETED)
  DELETE FROM [table_a] WHERE id IN (SELECT id FROM DELETED)
END

本質的に、テーブルaのレコードの削除を他の操作に入れ替えています。もちろん、削除だけでなく、SET NULLなどの実現も可能となります。

トリガーの機能はもっと幅広いと思いますが、今回はこのMultiple Cascade Paths問題についての解決案として導入してみました。同じ問題に会った方へのご参考になれれば嬉しいです。

以上です！