スライドショースライドショー

【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)に、外部キーを作成するサンプルコードを確認して見ましょう。はじめに親テーブルの部署テーブルを作成します。

    CREATE TABLE sample1_departments (
       department_no     char(5),     -- 部署番号
       department_name varchar(255),  -- 部署名
       PRIMARY KEY (department_no)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    
    -- テストデータ
    INSERT INTO sample1_departments VALUES('A0001', 'アプリ開発部');
    INSERT INTO sample1_departments VALUES('A0002', 'データベース開発部');
    INSERT INTO sample1_departments VALUES('B0003', 'Webデザイン部');

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

    CREATE TABLE sample1_employees (
        no int NOT NULL, -- 従業員番号
        department_no char(5), -- 部署番号
        last_name varchar(255), -- 名
        first_name varchar(255), -- 姓
        PRIMARY KEY (no), -- 主キー
        FOREIGN KEY(department_no) -- 外部キー
        REFERENCES sample1_departments(department_no) -- 部署テーブル.部署番号
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    
    -- テストデータ
    INSERT INTO sample1_employees VALUES('10001', 'A0001','侍','太郎');
    INSERT INTO sample1_employees VALUES('10002', 'A0002','侍','次郎');
    INSERT INTO sample1_employees VALUES('10003', 'B0003','侍','花子');

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

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

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

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

    sql_sql_foreign_key_2

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

    INSERT INTO sample1_employees VALUES('10004', 'C0004','侍','三郎');
    

    実行結果:

    ERROR 1452 (23000):
    Cannot add or update a child row: a foreign key constraint fails 
    (`employees`.`sample1_employees`, CONSTRAINT `sample1_employees_ibfk_1` 
    FOREIGN KEY (`department_no`) REFERENCES `sample1_departments` (`department_no`))
    

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

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

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

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

    sql_sql_foreign_key_3

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

    DELETE FROM sample1_departments WHERE department_no = 'A0001';
    

    実行結果:

    ERROR 1451 (23000):Cannot delete or update a parent row: 
    a foreign key constraint fails 
    (`employees`.`sample1_employees`, CONSTRAINT `sample1_employees_ibfk_1` 
    FOREIGN KEY (`department_no`) REFERENCES `sample1_departments` (`department_no`))
    

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

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

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

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

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

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

    CREATE TABLE sample2_employees (
      no int NOT NULL,          -- 従業員番号
     department_no char(5),    -- 部署番号
     last_name varchar(255),   -- 名
     first_name varchar(255),  -- 姓
     PRIMARY KEY (no) -- 主キー
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;
    

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

    ALTER TABLE sample2_employees ADD FOREIGN KEY(department_no) 
    REFERENCES sample1_departments(department_no);

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

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

    外部キーを削除する方法

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

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

    で削除できます。

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

    SHOW CREATE TABLE sample2_employees;
    

    実行結果:

    CREATE TABLE `sample2_employees` (
     `no` int(11) NOT NULL,
     `department_no` char(5) DEFAULT NULL,
     `last_name` varchar(255) DEFAULT NULL,
     `first_name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`no`),
      KEY `department_no` (`department_no`),
      CONSTRAINT `sample2_employees_ibfk_1` FOREIGN KEY (`department_no`) REFERENCES `sample1_departments` (`department_no`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
    

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

    削除しましょう。

    ALTER TABLE sample2_employees DROP FOREIGN KEY sample2_employees_ibfk_1;
    

    外部キーを確認:

    CREATE TABLE `sample2_employees` (
     `no` int(11) NOT NULL,
     `department_no` char(5) DEFAULT NULL,
     `last_name` varchar(255) DEFAULT NULL,
     `first_name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`no`),
      KEY `department_no` (`department_no`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

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

    まとめ

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

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

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

    LINEで送る
    Pocket

    無料でSEからWebエンジニアへ転職しませんか?



    侍エンジニア塾では、完全未経験の方から現在SEだけどプログラミングはやっていないという経験者まで、幅広い方々の人生を好転させるプログラミング指導を行ってきました。SEの方とお話していくなかで、

    • システムエンジニアという職業だけどコードが書けない
    • 事務作業が多くスキルがないため将来が不安
    • スクールに通うと完全未経験者と同じスタートになるからレベルが合わない
    という、すでに知識があるSEならではのお悩みがあることに気づきました。そんな方におすすめなのが、弊社の「転職コース 」です。

    弊社では、マンツーマンでレッスンを行いますので、現在お持ちの知識レベルからカリキュラムを作成いたします。さらにこちらの転職コースは無料で受講を始められて転職成功でそのまま卒業できるというとてもお得なコースとなっています。

    既に知識のあるSEといっても転職は年齢が若いほど受かりやすいため、まずは無料体験レッスンで今の現状や理想の働き方について一緒に考えていきましょう。

    まずは無料体験レッスンを予約する

    書いた人

    オオイシ

    オオイシ

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

    おすすめコンテンツ

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

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