日報 #90 - 外部キー制約によるアクションはトリガを有効にしません

諸事情による深夜作業の結果、日付の感覚が曖昧となり、
昨日のブログは無慈悲に消滅しました。
よくあることですね。

本日は、タイトルの通りでございます。 MySQLの外部キーとトリガー周りにおける転ばぬ先の杖を置いておきます。

説明すると簡単な話なので、まず結論から。
「外部キー制約によって発生する削除や変更アクションは、トリガーアクションを呼び出さない」
ということです。
これを知らなかったため、明け方作業中に膝から落ちるという事態に陥りました。
いや本当に勉強不足って怖いですね。。

さて具体的でシンプルな例でこの罠について説明します。
DB上に、ユーザ管理テーブルと、写真管理テーブル、そしてコメント管理テーブルがあったとします。

ある時、冗長ではありますが、パフォーマンス上の要求として、
ユーザの統計サマリーテーブルを作成しました。
このテーブルはユーザが放ったコメント数を管理しているテーブルだとします。

で、ある時、写真Aに平山がコメントしました。
コメント管理テーブルに対して、Aに平山がコメントつけたぞ、という挿入文が走ります。
ここで、統計サマリーテーブルは「そのイベント」をキャッチして「平山が放ったコメント数に+1する」という動作をします。
はい。
このイベント連動の動作をトリガーといいます。
便利ですね。

で、今度は、そのコメントを削除しました。
コメント管理テーブルには、写真Aについた平山のコメントを削除せい！という削除文が走ります。
統計テーブルはこの削除文に対してもトリガー反応し、「平山が放ったコメント数に-1をする」という動作をします。
これも便利ですね。
統計テーブルは常に平山のコメント数を追跡できています。

さて、これだけだと、
統計テーブルがキャッチするイベントトリガーは「レコードの削除・挿入」のタイミングと解釈できます。
後に説明しますが、この解釈が罠です。実はこの解釈は、厳密には誤りです。
何が間違っているのかを説明します。

その前にもう一つの主役、外部キー制約について触れておきます。
コメント管理テーブルと統計テーブルは、リレーショナルなテーブルです。
コメント管理テーブルは「写真」と「ユーザ」の情報に完全に依存していますし、
統計テーブルは「ユーザ」の情報に完全依存しているからです。
つまり、ユーザ平山自体が削除されたら、平山に紐付いたコメントレコードや、統計レコードは、何の意味もなくなるし、
写真Aが削除された時、それにまつわるコメントレコードには意味がなくなるわけです。
ということなので、コメント管理テーブルと統計テーブルは、ユーザ管理テーブルのユーザidや写真テーブルの写真idと厳密に連動している必要があります。 これをシステム的に実装したものが、外部キー制約といいます。
具体的に言えば、ユーザ管理テーブルの平山をもし削除したら、連動してコメント管理テーブルも統計テーブルの平山に関する情報も一斉に削除されます。
こうすれば「関係のないデータ」が完全に消えるわけですから、便利ですね。

で、この外部キー制約と、その前で説明したトリガーを一緒に使ったらどうなるのでしょうか。
話を進めます。
さて、改めて平山が写真Aにコメントしました。 コメントテーブルには「平山、Aにコメントす」という挿入文が走る。
それをイベントとして統計テーブルは「平山のコメント数に+1す」が走る。
そして、ある時、
写真Aが削除されました。
さて、想定する動作はどうなるでしょうか。
「写真A、削除す」が写真テーブルに走ります。
外部キー制約によって、コメントテーブルの写真Aに関するコメントが全て消え失せます。
さぁ、コメントが消えたのだから、
統計テーブルもイベントキャッチしてくれるはずです。
統計テーブルに「写真A削除によって消えた、平山のコメント数を-1す」というトリガは走るのでしょうか？

結論から言うと、この命令は走りません。
普通に「コメントを削除」した時は、統計テーブルのトリガは走ります。
が、外部キー制約によって「コメントが削除された」時に、統計テーブルのトリガは走りません。
本当に？
http://mysql.stu.edu.tw/doc/refman/5.1-olh/ja/innodb-foreign-key-constraints.html
ここに、こんな注意が書いてあります。
「現時点では、カスケード外部キーアクションが、トリガを有効にしません。」

これを読み損ねたせいで、ハマりました。
トリガは「レコードが削除されたら」というイベントではなくて、
「削除構文が走ったら」をイベントにしているわけです。
バイナリレベルでの実際のレコード削除がトリガーにはなってないんです。
「え？ってことはTRUNCATEでは？」とお思いの方もいらっしゃると思います。
はい、TRUNCATE構文でもDELTEトリガは走りません。

ハイ困りました。
結局、外部キーによる連動削除には期待せず、 写真レコードの削除時に、コメントテーブルへの関連レコードデリートトリガを作成しておけば、
デリーと構文が走るために、統計テーブルのトリガが連動します。
つまり、外部キーと同じ動作をするクエリ文をトリガーとして登録する必要があるわけです。
はて、外部キー制約とは一体何だったのか？と思うくらいの二度手間です。

少し長くなりましたが、半分社内向けの報告でもあるので、
何卒ご容赦。現場からは以上です。