第四章 キーレスエントリ（外部キー嫌い）

第四章 キーレスエントリ（外部キー嫌い）

##4.1 目的：データベースのアーキテクチャを単純化する
参照整合性は大事！
でもなんで外部キー使わないの？

・データの更新が、参照整合性制約と衝突してしまう。
・データーベース設計の柔軟性が極めて高いので、参照整合性制約をサポートできない。
・データーベースが外部キーのために作成するインデックスが、パフォーマンスに影響すると考えている。
・外部キーをサポートしないデーターベース製品を使っている。（MyISAMなど）
・外部キーを宣言する構文を調べなければならない。

##4.2 アンチパターン：外部キー制約を使用しない
外部キー制約を省略すれば、データーベース設計がシンプルになり、柔軟性が高まり、実行速度が速くなるかも…。でも代わりに参照整合性を保証するためのコードを書く責任が生じる。

####4.2.1 完璧なコードを前提にしている
・外部キー制約を設定しなかった場合、変更する前にselectクエリを実行し、参照性が壊れないことを確認しなければいけない。

例：行を挿入する前に、親の行の存在確認をしないといけない。

# 親行の確認
SELECT account_id FROM Accounts WHERE account_id = 1;

# アカウントを参照するバグレポート（子行）がinsertできる
INSERT INTO Bugs (reported_by) VAlUES (1);

例：行を削除する前に、親の行の存在確認をしないといけない。

# 親行を参照するバグレポートがあるか確認
SELECT bug_id FROM Bugs WHERE reported_by = 1;

# 子行がなかったらアカウント親を消すことができる
DELETE FROM Accounts WHERE account_id = 1;

・もし、account_id=1の利用者が知らないところで作業をしていて、上記の削除作業中に新しいバグレポートを登録していたら…？親のない不正な子レコードがBugsテーブル上にそのまま残ってしまう！

→対処策はBugsテーブルを明示的にロックしながらチェックを行い、アカウント削除後にロックを解除すること。
しかしこの種のロックを必要とするアーキテクチャでは同時接続ユーザーが増え、スケーラビリティ（システムの利用者や負荷の増大に応じて、柔軟に性能や機能を向上させられること）が求められるようになるにつれ、様々な問題に直面してしまう。

####4.2.2 ミスを調べなければならない
・問題1：参照が壊れたデータを探し出せる？

参照が壊れると、壊れた参照をチェックするスクリプトを書かなければいけなくなる…でも、どうやって？（´・ω・｀）孤児を探せない場合がでてくる。

####4.2.3 「私のミスではありません！」
・問題2：参照関係を担保できる？

誰かが作ったスクリプトでDBが変えられてしまうかも。。。
誰かが直接DBを書き換えてしまうかも。。。
→完璧なコードなんてありえない！
→参照がいつも正しく保たれているなんて信用できない！

####4.2.4 「キャッチ=22」なUPDATE
・外部キーがあると、参照関係のせいで挿入、更新、削除がめんどう！だから嫌い！
→めんどうだけど、ちゃんとした手順を踏めば挿入、更新、削除ができるよね。

・キャッチ=22（ジレンマ、パラドキシカルな状況）
親の行を更新するまで、子の行は更新できない←→子の行を更新するまで、親の行は更新できない

##4.3 アンチパターンの見つけ方
「あるテーブルには存在するけど、もう１つのテーブルには存在しない値を調べるクエリは、どう書けばいい？」
→孤児になった子の行を特定しようとしている。

「あるテーブルの値が、別のテーブルへの挿入に使われていることを簡単にチェックする方法はある？」
→親の行が存在しているか確認しようとしている。

「外部キー？データベースの実行速度を遅くするから、使ってはならないと教わったのだけど」
→パフォーマンスを気にして外部キーを省略しようとしている。

##4.4 アンチパターンを用いてもよい場合
MyISAMなど外部キー制約をサポートしていないデータベース製品を使う場合

##4.5 解決策：外部キー制約を宣言する
外部キー制約による参照整性の強制によってデータベースへの登録時点でこれらのミスを阻止できる。

CREATE TABLE Bugs (
  reported_by  BIGINT UNSIGNED NOT NULL,
  status       VARCHAR(20) NOT NULL DEFAULT 'NEW',
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
  FOREIGN KEY (status) REFERENCES BugStatus(status)
);

利点
・外部キーは不要なコードを書く手間を省く
・修正や保守の時間も大幅に削減できる

###4.5.1 複数テーブルの変更をサポートする

「カスケード更新」

CREATE TABLE Bugs (
  reported_by  BIGINT UNSIGNED NOT NULL,
  status       VARCHAR(20) NOT NULL DEFAULT 'NEW',
  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
);

カスケード更新を使うと…
・親の行の更新や削除が可能になり、さらにその行を参照しているあらゆる子の行もデータベースが適切に処理してくれるようになります。

####4.5.2 オーバーヘッド、…にはなりません

外部キー制約によって多少のオーバーヘッドがでるのは事実だけど、外部キーを使ったほうがより効率的になる。
・挿入、更新、削除の前に、チェックのためにSELECTクエリを実行する必要がない。
・複数テーブルの変更を防ぐために、テーブルをロックする必要がない。
・他の方法のように孤児が生じてしまうことがないので、データ品質管理用スクリプトを定期的に実行する必要がない。

#####☆外部キーは簡単に使え、パフォーマンスも改善する。あらゆるデータ変更における参照性合成の維持に役立つ！
#####☆データベースでのミスの発生を未然に防ぐために、外部キー制約を用いましょう。