【SQL入門】外部キーとは?主キーとの関係や作成方法について解説

こんにちは!システムエンジニアのオオイシです。

SQLの外部キー(FOREIGN KEY)をご存知ですか。外部キーの使いかたを覚えると、関連するテーブル間の整合性をデータベースに保証させることが可能です。

この記事では、

  • 外部キーとは?
  • 外部キーの役割について理解しよう
  • 外部キーを作成してみよう

  • といった、基本的な解説から

    • 外部キーは、親テーブルに存在しない値の登録をエラーにする
    • 外部キーは、子テーブルに存在する値の削除をエラーにする
    • 外部キーを後から追加する方法
    • 外部キーを削除する方法

    などの応用的な使い方関しても解説していきます。

    今回はそんな外部キーの使い方をわかりやすく解説します!

    外部キーとは?

    外部キー(FOREIGN KEY)とは、関連したテーブル間を結ぶために設定する列のことで、データの整合性をデータベースに保証させるために利用します。

    例えば、従業員テーブルに「従業員番号、部署番号、姓、名」の項目があるとして、部署テーブルの「部署番号」関連づけしたい場合に外部キーを利用します。

    sql_sql_foreign_key_1

    外部キーを設定して参照する側は子テーブル、設定元は親テーブルと言います。

    そんな外部キーの役割について次項で説明していきます。なお、主キー(PRIMARY KEY)については、こちらで詳しく解説していますので、ぜひ参考にしてください!

    【SQL入門】PRIMARY KEY(主キー)制約とは?追加や削除についても解説
    更新日 : 2019年4月1日

    外部キーの役割について理解しよう

    外部キーは、関連するテーブル間の整合性を保ちたい列に設定するデータベースの重要な機能です。部署テーブルと従業員デーブルを例に説明すると、

    • 部署テーブルに存在しない部署コードは、従業員テーブルに登録させない
    • 従業員テーブルに登録のある値は、部署テーブルから削除させない

    などの便利な制約を設定できます。

    外部キーの役割について理解いただけたところで、次は外部キーの作成方法について解説していきます!

    外部キーを作成してみよう

    外部キーを持つテーブルを作成するには、

    • FOREIGN KEY(列名) REFERENCES 親テーブル名(親列名)

    CREATE TABLEの最後に追加します。

    従業員テーブルの部署番号(department_no)に、外部キーを作成するサンプルコードを確認して見ましょう。はじめに親テーブルの部署テーブルを作成します。

    つづいて、従業員テーブルを作成するとともに、FOREIGN KEYで「部署番号(department_no)」に外部キーを設定します。

    以上で、従業員テーブルの「部署番号(department_no)」に外部キーを設定することができました。

    次項では、外部キー設定によって保証された整合性について確認していきましょう。

    外部キーは、親テーブルに存在しない値の登録をエラーにする

    外部キーを設定すると、親テーブルに存在しない値を登録した場合にはエラーになります。

    sql_sql_foreign_key_2

    次のサンプルコードで確認してみましょう。

    実行結果:

    このように、従業員テーブル(子テーブル)に登録するとエラーになることが確認できました。

    次項では、親テーブルのレコード(データ行)を削除した場合について解説していきます。

    外部キーは、子テーブルに存在する値の削除をエラーにする

    外部キーを設定すると、子テーブルに値が存在する、親テーブルのレコードを削除しようとするとエラーとなるため削除できません。

    sql_sql_foreign_key_3

    次のサンプルコードで確認してみましょう。

    実行結果:

    このように、部署テーブル(親レコード)のレコードはエラーになるため削除できないことが確認できました。

    もしも、データを削除したい場合は、従業員テーブル(子テーブル)のデータ削除する必要があります。次項では、外部キーを後から追加する方法について解説していきます!

    外部キーを後から追加する方法

    存在するテーブルに後から外部キーを設定するには、次のコマンドで作成できます。

    • ALTER TABLE 子テーブル名 ADD FOREIGN KEY(子の列名) REFERENCES 親テーブル名(親の列名)

    従業員テーブルの「部署番号(department_no)」に外部キーを設定するサンプルコードを確認してみましょう。はじめに外部キーの設定なしで従業員テーブルを作成します。

    つづいて、ALTER TABLE文で「部署番号(department_no)」に外部キーを設定します。

    このように、後から外部キーを設定することが可能です。

    次項では外部キーの削除方法についてみていきましょう。

    外部キーを削除する方法

    外部キーを削除するには、

    • ALTER TABLE テーブル名 DROP FOREIGN KEY 外部キーのID

    で削除できます。

    外部キーのIDは、実は外部キーの作成時に自動で命名されるので、名称を調べてから削除します。SHOW CREATE TABLEコマンドを使います。

    実行結果:

    この場合sample2_employees_ibfk_1が外部キーのIDになります。

    削除しましょう。

    外部キーを確認:

    このように、外部キーを削除することができました。

    まとめ

    いかかでしたか?今回はSQLの外部キー(FOREIGN KEY)について解説しました。

    外部キーは、関連のあるテーブル間の整合性をデータベースに保証させるために設定します。外部キーを使うと、間違ったデータの登録や削除を防いだり、関連するデータを一括に変更できるため、ぜひ活用してみてください。

    そして、外部キーの使い方を忘れてしまったらこの記事を確認してくださいね!

    LINEで送る
    Pocket

    最短でエンジニアを目指すなら侍エンジニア塾

    cta_under_bnr

    侍エンジニア塾は業界で初めてマンツーマンレッスンを始めたプログラミングスクールです。これまでの指導実績は16,000名を超え、未経験から数多くのエンジニアを輩出しています。

    あなたの目的に合わせてカリキュラムを作成し、現役エンジニア講師が専属であなたの学習をサポートするため効率よく学習を進めることができますよ。

    無理な勧誘などは一切ありません。まずは無料体験レッスンを受講ください。

    無料体験レッスンの詳細はこちら

    書いた人

    オオイシ

    オオイシ

    システムエンジニア歴15年のオオイシです!好物は Java と Ruby。プログラミング、システムアーキテクトからマネジメントに到るまでなんでも食べます。
    システム開発の相談やお困りごとがあればお気軽に
    [email protected] まで連絡頂けると幸いです

    おすすめコンテンツ

    あなたにぴったりなプログラミング学習プランを無料で診断!

    プログラミング学習の効率を劇的に上げる学習メソッドを解説