カスケード更新のパターン

SQLAP

4章 キーレスエントリ(外部キー嫌い)より

カスケード更新について考えてみる。

ON UPDATE、ON DELETE句には、RESTRICT, CASCADE, SET NULL, SET DEFAULT, NO ACTIONが指定できる。ただしMySQLにはSET DEFAULTは無い。

CREATE TABLE Bugs (
  ...
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
    ON UPDATE CASCADE
    ON DELETE RESTRICT,
  FOREIGN KEY (status) REFERENCES BugStatus(status)
    ON UPDATE CASCADE
    ON DELETE SET DEFAULT
);

Bugs.repotedy_byで使われているAccounts.account_idの行が削除された場合はエラーが発生する。これは適切だろうか。

Bugs.statusで使われているBugStatusの行が削除された場合は、Bugs.statusにはデフォルト値"NEW"がセットされる。これは適切だろうか。

別の例を考えてみる。サンプルデータベースではCommentsテーブルの外部キーにON UPDATE、ON DELETE句は設定されていない。

CREATE TABLE Comments (
  ...
  FOREIGN KEY (bug_id) REFERENCES Bugs(bug_id),
  FOREIGN KEY (author) REFERENCES Accounts(account_id)
);

ここにON UPDATE、ON DELETE句を書くとしたらどのように書くのが適切だろうか。

Bugsの行が削除された場合は、Comments.bug_idに適当なデフォルト値は無さそうなのでSET DEFAULTは無いだろう。またNULLをセットしても、どのバグに対するものかという情報を失ったコメントに意味は無さそうに思える。なので、エラーにするか、カスケードに削除するかどちらかがよさそうに思える。

一方、Accountsの行が削除された場合は、Comments.authorはどうだろう。やはり適切なデフォルト値は無さそうだが、NULLにセットするのはありそうだ。誰が書いたかという情報を失なっても、バグに対するコメント自体は残しておきたいと思うかもしれないからだ。mixiのコミュニティとかでよく見た、投稿者が空欄になったコメントと同じだ。 (但し、サンプルデータベースではComments.authourにNOT NULL制約が付いている)

こう感じるの裏側にはどんなパターンが隠れているのだろう。

  • Bugs.statusは遷移する状態を表しているので適切な初期状態に戻せば良い、と感じるのかもしれない

  • 「どのバグに対するコメントかわからないコメント」と「誰が書いたかわからないコメント」の情報量の違いをどう説明すればいいだろう

  • 「誰が書いたかわからなバグレポート」と「誰が書いたかわからないコメント」についてはどうだろう

リソース系とイベント系という言葉でパターンにできないかと思ったけど上手くいかなかった。

あとON UPDATEについては、主キーが更新されることなんてめったに無いのだけれどあるとすれば自然キーを使っているときに番号の割り当てルールが変わったときなんかだろう(ISBNの桁数が変わったとか)。このサンプルデータベースではaccount_idはSERIALなのでまずおこらないだろうけど。