外部キー制約

外部キー制約を設定する方法を確認します。基本の書式は次の通りです。

CREATE TABLE db_name.tbl_name
  (col_name data_type, ...,
  FOREIGN KEY (index_col_name, ...)
    REFERENCES tbl_name [(index_col_name, ...)]
);

外部キー制約とは、テーブルの指定したカラムに格納できる値を他のテーブルに格納されている値だけに限定するものです。参照される側のテーブルを親テーブル、参照する側のテーブルを子テーブルと呼びます。

親テーブルと子テーブルは簡略化して記載すると次のように定義されます。

親テーブル：

CREATE TABLE 親テーブル名(親カラム名 データ型) ENGINE=InnoDB;

子テーブル：

CREATE TABLE 子テーブル名(子カラム名 データ型,
  FOREIGN KEY (子カラム名)
  REFERENCES 親テーブル名(親カラム名)
) ENGINE=InnoDB;

例えば親テーブルとして商品一覧のテーブル、子テーブルとして売上テーブルを用意します。そして売上テーブルの中の商品カラムの値に格納できる値に対する制限として商品一覧テーブルの商品カラムに格納されているものに限定させたい場合などに利用します。

なお外部キー制約を設定する場合は次の点に注意して下さい。

サンプル

では実際に試してみます。次のようなテーブルを作成します。

親テーブル：

mysql> create table goods(
    -> id int, name varchar(10), index(name)
    -> ) engine=InnoDB;

子テーブル：

mysql> create table sales(
    -> id int, name varchar(10), d date, index(name),
    -> foreign key(name) references goods(name)
    -> ) engine=InnoDB;

まず親テーブルにデータを追加します。

続いて子テーブルにデータを追加します。外部キー制約が設定されていますので、子テーブルの「name」カラムには親テーブルの「name」カラムに格納されている値しか格納できません。まずは親テーブルに存在する値を追加してみます。

続いて親テーブルに存在しない値を指定してデータを追加してみます。

このように親テーブルに存在しない値を持つデータを追加しようとするとエラーが発生します。

親テーブルのカラムを更新/削除する場合

外部キー制約を設定した場合、親テーブルのカラムの値は子テーブルから参照されることになります。そこで親テーブル側のカラムの値を更新したり削除したりする場合には注意が必要となります。

ここではデフォルトの動作を確認しておきます。では親テーブルのカラムの値を1つ更新してみます。

このようにデフォルトでは親テーブルの中の外部キー制約の対象となっているカラムの値を更新しようとするとエラーとなります。削除しようとした場合も同様です。

次のページで更新や削除を行おうとした場合の動作を変更する方法を確認します。

DBOnline