【データベース】「外部キー」、「リレーション」について

外部キーって何となくＤＢ設計されて使っている方も多いと思いますが、具体的にどんなメリット、デメリットがあるのでしょうか。

そもそも外部キーとは？

テーブル同士のリレーションがどの列にあるか決めるもので、あるテーブルのキー（子テーブル）で、別テーブル（親テーブル）の主キーを参照することです。

「Foreign Key」を省略して、「FK」と呼んだりもします。

外部キーを使うためには、以下の制約があります。

子テーブルでは、外部キーに設定した値は主キーの必要はないが、データは一意にする必要がある。（もちろん主キーにしても良い）

メリット

子テーブルに、親テーブルに存在しない値を挿入させないこと。

これを「参照整合性」と呼びます。

これによって、テーブル間のデータの状態が正しく保たれるので、保守が楽になります。

開発時に、「参照整合性」を意識したコードを書く必要がないこと。

子テーブルに、データを挿入する前に、親テーブルにデータが存在するか確認する必要があります。

どちらかと言えば、これが最大のメリットでしょう。

デメリット

結論から申しますと、開発時において面倒となるケースが増えることでしょう。そこそこの数人単位で開発される方が多い場合は、ＤＢ設計も理解されていない方が参画することが多いですから、余計な面倒ごとを増やしたくありませんからね。

そもそも設定、設計自体が面倒。

設定や設計がめんどくさくなりますし、テーブル作成時のＳＱＬも複雑化して理解しずらくなります。

パフォーマンスが下がる？

外部キーを設定すると、パフォーマンスが下がるという説もあるそうです。本当かどうかは試していないです。

データの挿入、更新、削除も面倒になる。

挿入の場合

親テーブルにデータが存在していなければ、子テーブルに挿入ができません。

単体テスト等の機能レベルのテストでは特にそうですが、使いたいのは子テーブルの情報だけなのに、親テーブルまでデータを用意しなければならなくなる等データ作成の手間が増えます。

更新の場合

子テーブルの情報を更新する場合は、親テーブルに存在する値のみしか更新ができません。

削除の場合

親テーブルの外部キーとして設定されている情報を削除する場合は、先に子テーブルの情報を消す必要が出てくる。

よって、特にそうですが、開発やテスト等（主に単体テストレベル？）で、データの用意が面倒になりえます。

構文

CREATE文の最下行等に下記のように設定します。

ON DELETE設定

外部キーのデータを削除する際の挙動は「ON DELETE設定」にて行うことが可能です。それぞれ下記の挙動の違いがあるので業務に応じて使い分けるようにしましょう。

ON DELETE CASCADE	親テーブルのデータを削除したら子テーブルのデータも削除する設定です。
ON DELETE RESTRICT	親テーブルのデータを削除する際に子テーブルのデータを削除してないとエラーが出る設定です。
ON DELETE SET NULL	親テーブルのデータを削除する際に、子テーブルのデータをNULLにする設定です。

ON UPDATE設定

外部キーのデータを削除する際の挙動は「ON DELETE設定」にて行うことが可能です。それぞれ下記の挙動の違いがあるので業務に応じて使い分けるようにしましょう。

ON UPDATE CASCADE	親テーブルのデータを更新したら子テーブルのデータも更新する設定です。
ON UPDATE RESTRICT	親テーブルのデータを更新する際に子テーブルのデータを更新できないようにエラーが出る設定です。
ON UPDATE SET NULL	親テーブルのデータを更新する際に、子テーブルのデータをNULLにする設定です。

総合的に見て外部キーは使った方がよいのか？

総合的に見て、「外部キーは面倒だ…」と思う部分が多いので、使うのもありだと思いますが、使った方が良いです。

設計書がない案件とかだとどういう構造になっているかが後のエンジニアが見たときにわからなくなるためです。

リレーションの種類

種類	説明
1:1	実務ではあまり使いません。
1:多
多:多

1：多

• 従テーブルに外部キーを設定する都合上、主テーブルの方から必ず先に作成するようにしましょう。
• 主従関係にあるテーブルを作り、従テーブルの方に外部キーを付けます。

多：多

• 主従関係にあるテーブルの間に中間テーブルを作成するようにします。

中間テーブル

基本的にはアルファベット順に下記のように命名します。(ユーザーと部門の例)

• department_user