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

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

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

この記事では、

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

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

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

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

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

    目次

    外部キーとは?

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

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

    sql_sql_foreign_key_1

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

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

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

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

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

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

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

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

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

    • 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)について解説しました。

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

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

    この記事を書いた人

    【プロフィール】
    DX認定取得事業者に選定されている株式会社SAMURAIのマーケティング・コミュニケーション部が運営。「質の高いIT教育を、すべての人に」をミッションに、IT・プログラミングを学び始めた初学者の方に向け記事を執筆。
    累計指導者数4万5,000名以上のプログラミングスクール「侍エンジニア」、累計登録者数1万8,000人以上のオンライン学習サービス「侍テラコヤ」で扱う教材開発のノウハウ、2013年の創業から運営で得た知見に基づき、記事の執筆だけでなく編集・監修も担当しています。
    【専門分野】
    IT/Web開発/AI・ロボット開発/インフラ開発/ゲーム開発/AI/Webデザイン

    目次