FOREIGN KEY制約(外部キー制約を設定する)

テーブルを作成するときにカラムに FOREIGN KEY 制約(外部キー制約)を設定することで、対象のカラムに格納できる値を他のテーブルに格納されている値だけに限定することができます。ここでは MySQL における FOREIGN KEY 制約の使い方について解説します。

(Last modified: )

FOREIGN KEY制約(外部キー制約)とは

FOREIGN KEY制約(外部キー制約)とは親テーブルと子テーブルの2つのテーブル間でデータの整合性を保つために設定される制約です。次の図を見てください。

FOREIGN KEY制約(外部キー制約)とは(1)

参照する側の子テーブルにある部署カラムに対し、相手側として参照される側の親テーブルにある部署カラムとを指定して FOREIGN KEY 制約を設定します。するとこの2つのカラムでは整合性が保たれるように設定されます。

具体的には子テーブルにデータを追加するとき、 FOREIGN KEY 制約が設定されたカラムには、親テーブルのカラムに格納されている値しか格納することができなくなります。親テーブルに存在しない値を含むデータを追加しようとするとエラーとなります。

FOREIGN KEY制約(外部キー制約)とは(2)

また親テーブル側のデータに対して削除をしたり更新したりしようとしたとき、対象となる値がすでに子テーブル側で使用されている場合には、エラーとなったり同時に削除や更新を行ったりという選択を行えます。

FOREIGN KEY制約(外部キー制約)とは(3)

このように親テーブルと子テーブルの間で、値の整合性を取ることができるようにするのが FOREIGN KEY 制約(外部キー制約)です。

FOREIGN KEY制約の使い方

カラムに対して FOREIGN KEY 制約を設定は子テーブル側で行います。次の書式を使用します。 

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

FOREIGN KEY 制約を使用する場合は次の注意事項があります。

・親テーブルおよび子テーブルでは FOREIGN KEY 制約の対象となるカラムに対してインデックスが必要(作成されない場合は自動で作成される)
・親テーブルと子テーブルは同じストレージエンジンを使用する必要がある
・MySQLで FOREIGN KEY を使用できるストレージエンジンは InnoDB と NDB
・子テーブルの対象カラムと親テーブルの対象カラムは同じデータ型である必要がある。文字列型の場合、長さは同じである必要がないが、非バイナリ型の場合は文字セットと照合順序は同じである必要がある

親テーブル(参照されるテーブル)と子テーブル(参照するテーブル)は例えば次のような形になります。親テーブル側のインデックスはプライマリーキーにしてありますが、単なるインデックスでも構いません。 

親テーブル
CREATE TABLE 親テーブル名(親カラム名 データ型 PRIMARY KEY);

子テーブル
CREATE TABLE 子テーブル名
  (子カラム名 データ型,
   INDEX インデックス名(子カラム名), 
   FOREIGN KEY 外部キー名(子カラム名) REFERENCES 親テーブル名(親カラム名))

それでは実際に試してみます。親テーブル側は次のように作成しました。

create table divtbl(division varchar(10) primary key);

FOREIGN KEY制約の使い方(1)

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

insert into divtbl values('Sales');
insert into divtbl values('Accounting');
insert into divtbl values('Marketing');
insert into divtbl values('Research');

FOREIGN KEY制約の使い方(2)

次に子テーブル側は次のように作成しました。 division カラムに対して、参照先として divtbl テーブルの division カラムを対象とした FOREIGN KEY 制約を設定しています。

create table staff(id int, name varchar(10), division varchar(10), index div_index(division), foreign key fk_divisiono (division) references divtbl(division));

FOREIGN KEY制約の使い方(3)

子テーブルにデータを追加します。子テーブルの division カラムには親テーブルの division カラムに格納されている値しか格納することができません。まず問題のないデータをいくつか追加してみます。

insert into staff values(1, 'Yamada', 'Sales');
insert into staff values(2, 'Suzuki', 'Marketing');
insert into staff values(3, 'Okada', 'Sales');
insert into staff values(4, 'Hosoda', 'Research');

FOREIGN KEY制約の使い方(4)

次に親テーブルの参照先カラムにない値を含むデータを追加してみます。

insert into staff values(5, 'Inoue', 'Publicity');

Cannot add or update a child row: a foreign key constraint fails (`mydb`.`staff`, CONSTRAINT `fk_divisiono` FOREIGN KEY (`division`) REFERENCES `divtbl` (`division`)) というエラーが表示されてデータを追加することができませんでした。

FOREIGN KEY制約の使い方(5)

このように FOREIGN KEY 制約を設定することで、子テーブルの該当のカラムには親テーブルの該当カラムに格納されている値以外を格納することができなくなります。

外部キーに関する情報を取得する

INFORMATION_SCHEMA.KEY_COLUMN_USAGE テーブルを参照することで外部キーに関する情報を取得することができます。次のように実行してください。

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE where table_schema='mydb'\G

外部キーに関する情報を取得する(1)

1 つめが親テーブルに関するもの、 2 つめが子テーブルに関するものです。子テーブルの方を見ると FOREIGN KEY 制約が設定されているカラム( COLUMN_NAME )、親テーブルの参照先はそれぞれデータベース( REFERENCED_TABLE_SCHEMA )、テーブル( REFERENCED_TABLE_NAME )、カラム( REFERENCED_COLUMN_NAME )で確認することができます。

親テーブルの更新と削除を行った時にどうするのかを設定する

FOREIGN KEY 制約を設定することで、子テーブル側に追加できるデータに制限が発生しますが、親テーブル側でも既存のデータを削除したり更新したりする時に制限が発生します。

例として先ほど作成した 2 つのテーブルを使って親テーブルのデータを削除した場合にどうなるのかを確認してみます。親テーブルにある 'Sales' という値が格納されているカラムを削除してみます。

delete from divtbl where division='Sales';

Cannot delete or update a parent row: a foreign key constraint fails (`mydb`.`staff`, CONSTRAINT `fk_divisiono` FOREIGN KEY (`division`) REFERENCES `divtbl` (`division`)) というエラーが発生してデータを削除することができませんでした。

親テーブルの更新と削除を行った時にどうするのかを設定する(1)

削除しようとしたデータは小テーブルで既に同じ値のデータが格納されているため、親テーブル側でデータを削除してしまうと整合性が取れなくなってしまうためです。このように明示的に設定していない場合には、親テーブルのデータを削除したり更新したりするとエラーが発生します。

-- --

親テーブル側でデータの削除や更新を行った場合のふるまいについて設定を行う場合は次の書式を使用します。 

CREATE TABLE db_name.tbl_name
  (col_name data_type, ..., 
   FOREIGN KEY [index_name] (col_name, ...) 
   REFERENCES tbl_name (col_name, ...)
   [ON DELETE reference_option] [ON UPDATE reference_option])

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

データを削除した場合のふるまいは [ON DELETE reference_option] で設定します。またデータを更新した時のふるまいは [ON UPDATE reference_option] で設定します。どちらか 1 つ設定することも両方設定することもできます。

設定可能な値は次の5つです。設定を行わなかった場合は RESTRICT が設定された場合と同じになります。

RESTRICT
親テーブルに対して削除または更新を行うとエラーとなります。設定を省略した場合は RESTRICT を設定したのと同じです。

CASCADE
親テーブルに対して削除または更新を行うと、子テーブルで同じ値を持つカラムのデータに対して削除または更新を行います。

SET NULL
親テーブルに対して削除または更新を行うと、子テーブルの同じ値を持つカラムの値が NULL になります。

NO ACTION
親テーブルに対して削除または更新を行うとエラーとなります。 RESTRICT を設定した場合と同じです。

SET DEFAULT
現在この設定を行うとテーブルの作成が行えません。

それでは試してみます。 RESTRICT および NO ACTION については設定を行わなかった場合と同じなので、先ほど試したのと同じ結果となります。そこで CASCADE と SET NULL を試してみます。

ON DELETE CASCADE / ON UPDATE CASCADE

親テーブル側は次のように作成しました。親テーブルにデータを追加してあります。

create table divtbl(division varchar(10) primary key);

insert into divtbl values('Sales');
insert into divtbl values('Accounting');
insert into divtbl values('Marketing');
insert into divtbl values('Research');

親テーブルの更新と削除を行った時にどうするのかを設定する(2)

次に子テーブル側は次のように作成しました。 division カラムに対して、参照先として divtbl テーブルの division カラムを対象とした FOREIGN KEY 制約を設定しています。また親テーブルのデータの削除時および更新時のふるまいとして CASCADE を設定しました。

create table staff(id int, name varchar(10), division varchar(10), index div_index(division), foreign key fk_division (division) references divtbl(division) on delete cascade on update cascade);

親テーブルの更新と削除を行った時にどうするのかを設定する(3)

子テーブルにデータを追加します。そのあとで SELECT 文でデータを取得してみます。

insert into staff values(1, 'Yamada', 'Sales');
insert into staff values(2, 'Suzuki', 'Marketing');
insert into staff values(3, 'Okada', 'Sales');
insert into staff values(4, 'Hosoda', 'Research');

親テーブルの更新と削除を行った時にどうするのかを設定する(4)

それでは最初に親テーブルのカラムの値が 'Marketing' のデータを削除します。

delete from divtbl where division='Marketing';

親テーブルの更新と削除を行った時にどうするのかを設定する(5)

ON DELETE CASCADE が設定してあるので、親テーブルで削除したデータと同じ値を持つ子テーブルのデータが自動的に削除されます。子テーブルのデータを改めて取得すると、データが削除されていることが確認できます。

select * from staff;

親テーブルの更新と削除を行った時にどうするのかを設定する(6)

次に親テーブルのカラムの値が 'Sales' のデータを 'Business' に更新してみます。

update divtbl set division='Business' where division='Sales';

親テーブルの更新と削除を行った時にどうするのかを設定する(7)

ON UPDATE CASCADE が設定してあるので、親テーブルで更新したデータと同じ値を持つ子テーブルのデータが自動的に更新されます。子テーブルのデータを改めて取得すると、データが更新されていることが確認できます。

select * from staff;

親テーブルの更新と削除を行った時にどうするのかを設定する(8)

ON DELETE SET NULL / ON UPDATE SET NULL

親テーブル側は次のように作成しました。親テーブルにデータを追加してあります。

create table divtbl(division varchar(10) primary key);

insert into divtbl values('Sales');
insert into divtbl values('Accounting');
insert into divtbl values('Marketing');
insert into divtbl values('Research');

親テーブルの更新と削除を行った時にどうするのかを設定する(9)

次に子テーブル側は次のように作成しました。 division カラムに対して、参照先として divtbl テーブルの division カラムを対象とした FOREIGN KEY 制約を設定しています。また親テーブルのデータの削除時および更新時のふるまいとして SET NULL を設定しました。

create table staff(id int, name varchar(10), division varchar(10), index div_index(division), foreign key fk_division (division) references divtbl(division) on delete set null on update set null);

親テーブルの更新と削除を行った時にどうするのかを設定する(10)

子テーブルにデータを追加します。そのあとで SELECT 文でデータを取得してみます。

insert into staff values(1, 'Yamada', 'Sales');
insert into staff values(2, 'Suzuki', 'Marketing');
insert into staff values(3, 'Okada', 'Sales');
insert into staff values(4, 'Hosoda', 'Research');

親テーブルの更新と削除を行った時にどうするのかを設定する(11)

それでは最初に親テーブルのカラムの値が 'Marketing' のデータを削除します。

delete from divtbl where division='Marketing';

親テーブルの更新と削除を行った時にどうするのかを設定する(12)

ON DELETE SET NULL が設定してあるので、親テーブルで削除したデータと同じ値を持つ子テーブルのデータが NULL に更新されます。子テーブルのデータを改めて取得すると、該当のデータのカラムの値が NULL に更新されていることが確認できます。

select * from staff;

親テーブルの更新と削除を行った時にどうするのかを設定する(13)

次に親テーブルのカラムの値が 'Sales' のデータを 'Business' に更新してみます。

update divtbl set division='Business' where division='Sales';

親テーブルの更新と削除を行った時にどうするのかを設定する(14)

ON UPDATE SET NULL が設定してあるので、親テーブルで削除したデータと同じ値を持つ子テーブルのデータが NULL に更新されます。子テーブルのデータを改めて取得すると、該当のデータのカラムの値が NULL に更新されていることが確認できます。

select * from staff;

親テーブルの更新と削除を行った時にどうするのかを設定する(15)

目的にあわせて親テーブルのデータを削除した場合と更新した場合のふるまいについて設定してください。

-- --

FOREIGN KEY 制約の使い方について解説しました。

( Written by Tatsuo Ikura )

プロフィール画像

著者 / TATSUO IKURA

これから IT 関連の知識を学ばれる方を対象に、色々な言語でのプログラミング方法や関連する技術、開発環境構築などに関する解説サイトを運営しています。