SQLアンチパターンのキーレスエントリパターンのデメリットと解決方法を選択

こちらの記事では、「SQLアンチパターン」に登場するキーレスエントリについてまとめたものになります。

キーレスエントリとは

キーレスエントリとは、テーブル定義をする際に外部キー制約を省略するアンチパターンです。

外部キー制約を省略することで、データベース設計はシンプルで柔軟になりますが、参照整合性を保証するアプリケーション側の実装が必要になってきます。

キーレスエントリのデメリット

完璧なコードを前提にしている

参照整合性を保証するために用いられる解決策は、データの関連付けを常に維持するためのソースコードを書くことです。

外部キー制約を設定しなかった場合は、変更を適応する前に、変更によって参照が壊れないことを確認する必要があります。

レコードを追加する場合は、参照元のキーが存在するかどうかを SELECT 文で確認してから INSERT をする必要があります。

また、レコードを削除する場合は、子の行が存在しないことを確認してから DELETE をする必要がああります。

さらに、それぞれの SQL 文だけでは、参照整合性は保証されず、アプリケーションを他の人が使っていて子の行がないことを確認した直後に他の人が INSERT を行うことがあります。

そうなるとせっかく参照整合性がないことを確認して DELETE をしようとしたのにも関わらず、削除する前に参照関係が生まれてしまい、整合性がなくなってしまいます。

その対処法は、テーブルをロックすることです。テーブルをロックすることで、一人のユーザーしかそのテーブルのあらゆる操作ができなくなるので、参照整合性を保つことができるようになります。

ただし、ロックとロック解除を明示的に行う必要が出てきてしまうのと、同時接続ユーザーが増え、スケーラビリティが求められるにつれ、様々な問題に直結します。

ミスを調べなければならない

例えば、存在しないステータスが Bugs テーブルのレコードに存在すると仮定します。
Bugs テーブルで不正なレコードを特定するためには、以下のような SQL クエリです。

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 する場合、親の行を更新するまで、子の行は更新できません。

また、親の更新も、参照する子の値を更新する前には実行できません。二つの処理を同時に行う必要がありますが、それは不可能です。

キーレスエントリの解決方法

外部キー制約を宣言する

外部キー制約による参照整合性の強制によって、ミス防止をデータベース設計に適用できます。

データ不整合を検出してから修正するのではなく、データベースへの登録時点でミスを阻止することができます。

また、外部キー制約を入れることによって、行を確認するアプリケーション側のソースコードを省くことができるため、結果として修正・保守の時間を削減することができます。

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)
);

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

外部キーには、アプリケーションでは実装できない「カスケード更新」という機能があります。

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

外部キー制約に ON UPDATE 句や ON DELETE 句を宣言することで、カスケード処理の内容を定義できます。

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

まとめ

外部キー制約をすることで、更新・挿入・削除時の行チェックが必要なくなります。また、複数テーブルを変更するためのテーブルロックが必要なくなる。さらに、不正なレコードが発生しないので、値チェックをする必要がなくなります。