SQLアンチパターン勉強会 第四回：キーレスエントリ

#はじめに
本エントリーは某社内で実施するデザインパターン勉強会向けの資料となります。

本エントリーで書籍「SQL アンチパターン」をベースに学習を進めます。書籍上でのサンプルコードはMySQLですが、本エントリーでのサンプルコードはT-SQLに置き換えて解説します。

#キーレスエントリとは
外部キー制約を省略するアンチパターンです。
外部キー制約を省略すれば、データベース設計はシンプルになりますが、参照整合性を保証するソースコードを書く必要があります。

##参照整合性を保証するコード
参照整合性を保証するために用いられる解決策は、データの関連付けを常に維持するためのソースコードを書くことです。
外部キー制約を設定しなかった場合は、変更を適応する前に、変更によって参照が壊れないことを確認する必要があります。

以下に例を示します。
テーブルAccountsが親、テーブルBugsが子の関係です。

1.レコードを追加する場合
1.1.アカウントが存在することを確認する

SELECT account_id FROM Accounts WHERE account_id = 1;

1.2.レコードを追加する

INSERT INTO Bugs (reported_by,date_reported) VALUES (1,'2018-09-19');

2.レコードを削除する場合
2.1.子テーブルで削除するアカウントが使われていないことを確認する

SELECT bug_id FROM Bugs WHERE reported_by = 1;

2.2.アカウントを削除する

DELETE FROM Accounts WHERE account_id = 1;

上記の場合でも、2.1.と2.2.の間でaccount_id=1のアカウント利用者がBugsテーブルにレコードを追加した場合、存在しないアカウントがBugsテーブルのレコードに存在することになります。
対策案として、関連するテーブルをロックし、処理後に解除する方法がありますが、利用者が増えるとロックの解除待ちで処理時間が伸びてしまいます。

##不正レコードを抽出する
ここでは存在しないステータスがBugsテーブルのレコードに存在する場合を想定します。
Bugsテーブルで不正なレコードを特定するためには、以下のようなクエリです。

SELECT b.bug_id, b.status
FROM Bugs b LEFT OUTER JOIN BugStatus s
ON b.status = s.status
WHERE s.status IS NULL

テーブルBugStatusが親、テーブルBugsが子の関係です。
ここで抽出された不正レコードに対して何らかの対処が必要になります。
また、上記のようなチェックをBugsテーブルが参照しているテーブルで全てで行い、不正レコードのチェックを定期的に実施する必要があります。

##アップデートのジレンマ
開発者が外部キーを避けるのは、複数のテーブルの関連し合う列を更新する際に、外部キー制約が邪魔になると感じるからです。
親テーブルのレコードを削除する場合、外部キー制約違反を避けるためには、先に子テーブルのレコードを削除する必要があります。

DELETE FROM Bugs WHERE status ='bogus'; --子テーブル
DELETE FROM BugStatus WHERE status ='bogus'; --親テーブル

ただし、アップデートをする場合は問題です。
親テーブルのレコードを更新するまで、子テーブルのレコードは更新できません。
また、子テーブルのレコードを更新するまで、親テーブルは更新できません。
両方を同時に変更する必要がありますが、2つの更新処理を同時に実行することはできません。

UPDATE Bugs SET status = 'INVALID' WHERE status='bogus'; --子テーブル
UPDATE BugStatus SET status = 'INVALID' WHERE status='bogus'; --親テーブル

#アンチパターンを用いざるを得ない場合
外部キーを用いた関連付けを行えない、極端に柔軟なデータベース設計の場合、他のアンチパターンの陥る可能性があります。

• EAV
• ポリモーフィック関連

#解決策：外部キー制約を宣言する
外部キー制約による参照整合性の強制によって、データ不整合を検出してから修正するのではなく、データベースへの登録時点でミスを阻止します。

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

外部キーを設定することで、レコードを確認するソースコードを省くことができるため、結果として、修正や保守の時間を削減できます。

##複数テーブル変更のサポートを有効にする
親子関係のあるレコードの変更や削除をするために、カスケード更新という機能があります。
「アップデートのジレンマ」で紹介した、外部キー制約違反で更新できない問題はこれで解決できます。

CREATE TABLE Bugs (
  ～省略～
  reported_by       BIGINT NOT NULL,
  status            VARCHAR(20) NOT NULL DEFAULT 'NEW',
FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
	ON DELETE NO ACTION,
FOREIGN KEY (status) REFERENCES BugStatus(status)
  	ON UPDATE CASCADE
	ON DELETE SET DEFAULT

ON DELETE NO ACTION は子テーブルが参照しているレコードは親レコードで削除できなくなります。
ON DELETE SET DEFAULT は親テーブルでレコードを削除したとき、このレコードを参照する子テーブルのレコードにデフォルトの値をセットします。

#まとめ
外部キー制約を設定することによって以下のメリットがあります。

• 更新、挿入、削除の前にレコードをチェックする必要がない
• 複数テーブルを変更するために、テーブルをロックする必要がない
• 不正なレコードが発生しないため、レコードをチェックする操作が必要ない