MySQL InnoDBの外部キー

InnoDBの利用

外部キーとは

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

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

書籍テーブル
フィールド名 データタイプ
書籍コード INT(10) PRIMARY KEY
書籍名 VARCHAR(100)
著者コード INT(10)
CREATE TABLE 書籍テーブル (
    書籍コード INT(10) PRIMARY KEY,
    書籍名 VARCHAR(100),
    著者コード INT(10)
) type=innodb;
著者テーブル
フィールド名 データタイプ
著者コード INT(10) PRIMARY KEY
著者名 VARCHAR(100)
CREATE TABLE 著者テーブル (
    著者コード INT(10) PRIMARY KEY,
    著者名 VARCHAR(100)
) type=innodb;

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

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

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


外部キー制約の作成

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

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

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

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

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

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


外部キー利用時の注意点

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

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

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

Copyright©2001-2021 釣ったよ! All Right Reserved.    sg@tsuttayo.jpn.org