MySQL InnoDBの外部キー

外部キーとは

2つのテーブルがあり、それぞれが親子関係になっているような場合、データ更新時に自動的にデータ・チェックをしてくれる機能です。

たとえば、「書籍テーブル」「著者テーブル」という2つのテーブルで考えてみます。

このような場合、「著者テーブル」は親であり、「書籍テーブル」は子になっています。

「書籍テーブル」の著者コードは、「著者テーブル」の著者コードにレコードが存在している必要があります。 しかし、実際には「著者テーブル」に存在しない著者コードを「書籍テーブル」にレコード追加できてしまいます。

外部キー制約を利用すると、こういった「親にないレコードを子供に追加」することや「子供が存在するのに親のレコードを削除」するなど、本来は有り得ないデータ変更をMySQLサーバが自動チェックしてくれるようになります。

外部キー制約の作成

上記の例では、次のようにして外部キー制約を設定できます。

外部キーを作成する場合、子供側にキーが必要なので、まずキーを作ります。
ALTER TABLE 書籍テーブル ADD KEY (著者コード);

外部キーを作ります。
ALTER TABLE 書籍テーブル ADD FOREIGN KEY (著者コード) REFERENCES 著者テーブル (著者コード)

これで外部キー制約が設定されています。
なお、複数項目で外部キーを作成する場合は、項目名を並べればＯＫです。

さて、ここで外部キー制約が働くかみてみましょう。
たとえば、著者テーブルに存在しないデータを書籍テーブルに追加してみます。
INSERT INTO 書籍テーブル (書籍コード,書籍名,著者コード) VALUES (10, '釣ったよ', 100);
これはエラーになると思います。

次のようにして親のテーブルである著者テーブルにデータを追加すると、子供の書籍テーブルにはデータを追加できるようになります。
INSERT INTO 著者テーブル (著者コード,著者名) VALUES (100, 'sg');
INSERT INTO 書籍テーブル (書籍コード,書籍名,著者コード) VALUES (10, '釣ったよ', 100);

外部キー利用時の注意点

外部キーを設定すると、データの自動チェックがされるようになるので便利ですが、mysqldumpでバックアップするようなときに困ります。

mysqldumpでバックアップしたデータを復元するとき、外部キー制約に引っかかってエラーになります。

だから、外部キーを設定する場合には単純なmysqldumpを使うのではなく、テーブル別にバックアップするなど、何らかのスクリプトが必要だと思います。