トップ > SQL > 外部キー制約【SQLアンチパターンまとめ】

外部キー制約【SQLアンチパターンまとめ】

SQL

外部キー制約とは何か？

外部キー制約とは、SQLのテーブルに課す制約の一種。

外部キー制約を設定したカラムは、設定時に指定した親テーブルの特定のカラムにデータが存在するかどうかを、CRUDが発生するたびにチェックしてくれる

制約を設定する際には

  1. 対象となる「子」のテーブルと

  2. より強い権限を持つ「親」テーブルを指定する

Create table文発行の際に設定し、次のような予約語で設定する

FOREGIN KEY (カラム名） REFERENCES 親テーブル(カラム名)

外部キー制約具体例

今、Bugsというテーブルには、reported_byカラムが存在し、次の条件を満たすとする

  • CRUDの際には、Acountsテーブルのacount_idカラムにデータが存在することをチェックする

その場合は、次のようなCREATE TABLE句で設定できる

CREATE TABLE Bugs {

    reported_by     BIGINT UNSINED NOT NULL,

    status          VARCHAR(20) NOT NULL DEFAULT 'NEW'

    FOREGIN KEY (reported_by) REFERENCES Acounts(acount_id) 

        ON UPDATE CACADE,

        ON DELETE RESTRUCT,

    FOREGN KEY (status) REFERENCES BugStatus(status)

        ON UPDATE CASCADE

        ON DELETE SET DEFAULT

}

この設定をしたBugsのreported_byカラムは、Acountsのacount_idにデータが必ず存在するかどうかを常にチェックされる。

外部キー制約のデメリット

しかし、外部キー制約をつけないように進める考え方(外部キーのデメリット)もあります。

それは主に次のような考え方です。

  • パフォーマンスが悪くなる

  • ストレージエンジンが古いタイプでパフォーマンスに影響が出てしまいそう。

また、次のようなデメリットも外部キーを嫌がる理由の一つだ

  • データを更新したくても、外部キー制約のせいで更新できない

これはどういう場合かというと、

  • Bug全体を管理するBugsテーブルが存在する

  • バグのステータスを管理するBugStatusという二つのテーブルが存在する

  • Bugsテーブルが参照整合性としてBugStatusを参照している場合

という条件の場合、次のように子テーブルであるBugsを柔軟に更新できないという点だ。

UPDATE Bugs SET status = 'INVALID'

WHERE status = 'BOGUS'

// =>エラー！

異なる二つの更新処理を同時に行うことはできない。

このことでデータベースの柔軟性を損なってしまうという考えがある。

以上が外部キー制約のデメリットである。

外部キー制約のメリット(外部キー制約を使わない場合のリスク)

外部キー制約を頑なに使わない場合、さまざまなリスクが生じる。

特に厄介なのがデータベースを利用しているアプリケーションが、参照整合性を保つための完璧なコードを前提としているという点だ。

つまり、 「カラムの値は必ず、特定のカラムに存在すること」ということが保証されているのをプログラム側で完璧なロジックを組まなければならないのだ。

ケース1:親の行を確認してから、この行を追加しなければならない

  • 行の挿入時には外部キー列の値が参照先のテーブルの既存の値を参照していることを確認すること

  • 言い換えると、子の行の挿入時には親の行の存在を確認すること

が必要になってくる。

具体的には、



-- 確認用のクエリ

-- Acountsにacount_idが1の値が必ず入っていることを確認してから

SELECT acount_id FROM Acounts WHERE acount_id=1;



-- 挿入

-- Bugsのreported_byに1が入る行を追加する。

INSERT INTO Bugs (reported_by) Values (1);

というように、子(Bugs)の行の挿入時には親(Acounts)の行の存在を確認することを強いられる。

ケース2:親の行の削除の際には子の行にデータが存在しないことを確認しなければならない

外部キー制約を頑なに使わない場合、

親の行の削除の際には子の行のアカウントが存在しないことを確認しなければならない。



-- 確認用のクエリ

-- 子の行にidが1のアカウントが存在しないことを確認してから

SELECT bug_id FROM Bugs WHERE reported_by = 1;



-- 更新用のクエリ

-- 親のカラムを削除しなければならない

DELETE FROM Acounts WHERE acount_id = 1;

これらのコードを全てのデータベースを利用するプログラムに要求する必要があるが、これは現実的ではない

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

例）あなたは研究所の設備を予約するシステムを管理するシステム管理者です。

ある時こんなクレームが来ました。

「大変だ。研究所の設備を二人のマネージャーが同じ日に予約してしまっているんだ。どうしてこんなことになってしまっているか調べてくれないか？」

そのアプリケーションはMySQLを使った設備管理アプリだったが、デフォルトのストレージエンジンがMyISAMであった。

このストレージエンジンは参照整合性の強制を行う仕組みがなく、したがってデータベースのテーブル同士でリレーションシップ（関連するテーブル）があったとしても、参照整合性を使うことができなかった。

結果、不具合が存在する前提で早めに検知するための定期実行スクリプトを用意して「子テーブルで孤児になっている行」など、不正なデータを削除するクレンジング的なスクリプトを書く必要が出てきた。

しかしこのスクリプトもずっと活用することもできず、品質管理用のクエリの数も増え続け、スクリプトの実行時間も増えるようになって、いよいよ収集がつかなくなってきている。

どうしてこんなことになったのか。

それはひとえに、外部キーを使うことなく手動でのクレンジングを行ったが、それはいずれくる限界に耐えられる方法ではないからだ。

このように、外部キーを使わないことによるテーブルの精度の低下をキーレスエントリーと呼ぶ。

教訓：テーブルのカラムの設定と同じくらいテーブル同士のリレーションシップは重要

テーブルのカラムの設定と同じくらいテーブル同士のリレーションシップは重要です。

特に参照整合性を担保するための外部キー制約は適切なデータベースの運用において極めて重要。

ある列に外部キー制約を付け加えるときのメリットとして、「この列の値が親テーブルの主キーやユニークキーに存在しなくてはならない」と言うことを強制して、その規約に反する時はエラーを返してくれることがある。

やるべきこと:外部キー制約とカスケード更新がテーブルを健全な状態に保つ

Bugsは子テーブルの存在で、親テーブルはBugStatusとAcountsがある。

CREATE TABLE Bugs {

    reported_by     BIGINT UNSINED NOT NULL,

    status          VARCHAR(20) NOT NULL DEFAULT 'NEW'

    FOREGIN KEY (reported_by) REFERENCES Acounts(acount_id) 

        ON UPDATE CACADE,

        ON DELETE RESTRUCT,

    FOREGN KEY (status) REFERENCES BugStatus(status)

        ON UPDATE CASCADE

        ON DELETE SET DEFAULT

}

ポイントは二つ

REFERENCESとCASCADEだ。

REFERENCES:外部参照によるチェック

これは外部キーを設定するためのキーワードで他のデータベースとの主キーとの関連を表している。

これによっておその場限りのアドホックなテーブルの更新も外部キー制約から逃れることはなく、データベースは健全な状態を保つことができます。

CASCADE:芋づる式のCRUD

外部キーの作成時に CASCADE を指定した場合、Pervasive PSQL で DELETE CASCADE 規則が使用されます。

ユーザーが親テーブルの行を削除すると、Pervasive PSQL によって従属テーブルの対応する行が削除されます。

補足

title:外部キー制約のデメリットとメリット

description:外部キー制約を使用することで発生するデメリットとメリットを解説。結論を述べると、外部キー制約はテーブルを健全な状態に保つ役割を果たしている。データベースを利用しているアプリケーションが、参照整合性を保つための完璧なコードを前提としているような状態は避けるべきである。

img:https://www.oreilly.co.jp/books/images/picture_large978-4-87311-589-4.jpeg

category_script:page_name.startswith("30")

記事改修:https://techblog.short-tips.info/sql/3004hate_reference_key.md

外部キー制約 デメリット  32  180 17.8%   3.1

外部キー デメリット    10  36  27.8%   4

外部キー 使わない   9   54  16.7%   3.9

on delete cascade アンチパターン 7   34  20.6%   2.7

外部キー メリット   3   94  3.2%    6.9

外部キー制約 メリット 3   23  13% 3.7

外部キー制約  2   75  2.7%    37

外部キー 必要性  1   12  8.3%    8.1

sqlアンチパターン    1   11  9.1%    48.6

外部キー 重複 1   2   50% 22.5

before_title:外部キー制約【SQLアンチパターンまとめ】

before_other:記事全体の修繕

page:https://minegishirei.hatenablog.com/entry/2023/02/11/194229