外部キーと参照整合性を理解しよう！

「外部キーと参照整合性」っていきなりですが、先ずデータベースにおいて以下のような問題のケースを考えてみましょう！

受注テーブルの顧客コードは外部キーとして顧客テーブルを参照してます。顧客テーブルから「C商事」の行が間違って削除されてしまうと受注テーブルの受注No0003の行は顧客が不明な状態になってしまいます。

上記の例（図の上部）にあるように外部キーが指し示す先にきちんと行が存在してリレーションシップが成立していることを参照整合性と言います。

一方、図の下部にあるように参照先が無い異常な状態に陥ってしまうことを「参照整合性の崩壊」と言われ、データベースを利用するに際して発生してはいけない状態のことです。

今回はこの参照整合性を保つために外部キーに何を設定すべきか学んでみましょう！

参照整合性が崩壊するパターンとは？

では、この参照整合性の崩壊を引き起こしてしまうパターン、つまりSQLにおけるデータ操作は何でしょうか？

これは参照元と参照先それぞれのSQLデータ操作によって引き起こされ全部で4つのパターンがあります。

①、②は分かり易いですが、③、④のように参照元のテーブル内のデータ操作でも崩壊が起こってしまうことを覚えておきましょう！

このようなデータ操作はやってはいけないことなのですが、SQLによるデータ操作を人間が行う限りミスがゼロにはなりません。では、どうすればよいでしょうか？それは、間違ったデータ操作を行った時にエラーになれば良いのです。そのための制約をデータベースに設定してあげれば良いのです。

この制約のことを「外部キー制約」と呼ぶので、次はこの制約を理解しましょう！

外部キー制約とは？

改めて外部制約キーとは、参照整合性が崩壊するようなデータ操作をしようとした場合にエラーを発生させ、強制的にSQLの処理を中断させるために事前に設定しておく制約のことです。

この制約ですが、参照元のテーブルの外部キーの列に設定します。

テーブルを作成するときに設定するので、SQLにおける「CREATE TABLE文」で外部キーの制約をかけるには以下のような構文になります。

外部キー制約の指定方法

CREATE　TABLE　テーブル名（
　　　　列名　型　REFERENCES　参照先テーブル名（参照先列名）
　　　　：
）

先程の例にあった「受注テーブル」に外部キー制約をかけるには以下のようになります。

CREATE　TABLE　受注テーブル（
　　受注No　　　INTEGER　 NOT NULL,
　　受注日付　　 DATE　　　NOT NULL,
　　顧客コード　 INTEGER　REFERENCES　顧客テーブル（顧客コード）
）

また、CREATE TABLE文の最後にまとめて定義することも可能となります。

この場合は「FOREIGN KEY」で制約を付ける列を指定するようになります。

まとめ

今回はデータベースのリレーションシップにおける参照整合性と崩壊を防ぐための制約に関して調べてみました。

人間が行う限り誤ったデータベースの操作を行ってしまうことは防げないので、テーブルを作成する際に外部キーの制約を設定してあげることで機械的に問題を防げるようになっているのですね。

ただし、この外部キーの制約もテーブル設計をする上で人間が正しく設定してあげないと意味がないので、きちんと理解しておくことが重要ですね！

人間界の整合性もよく崩壊している現場を見ますが、制約によって未然に防ぎたいものですね...（汗）

以上です！