MySQLの外部キー（FOREIGN KEY）とは？作成方法と確認方法を実例付きで解説

外部キー（FOREIGN KEY）とは

外部キーは、テーブル間の整合性（参照整合性）を維持するために使用される制約の一種です。あるテーブルに外部キーを作成すると、そのテーブルは「子テーブル」と呼ばれ、参照先となるもう一方のテーブルは「親テーブル」と呼ばれます。

親テーブル側では、外部キーの参照先となる列が主キー（PRIMARY KEY）として機能します。それでは、実際にテーブルを作成しながら手順を見ていきましょう。

子テーブルの作成

まず、外部キーを持つことになる子テーブル「StudentEnrollment」を作成します。

mysql> create table StudentEnrollment
   -> (
   -> StudentId int,
   -> StudentName varchar(200),
   -> StudentFKPK int
   -> );
Query OK, 0 rows affected (0.91 sec)

親テーブルの作成

続いて、参照先となる親テーブル「College」を作成します。ここでは「StudentFKPK」列を主キーとして設定しています。

mysql> create table College
   -> (
   -> StudentFKPK int,
   -> CourseId int,
   -> CourseName varchar(200),
   -> CollegeName varchar(200),
   -> primary key(StudentFKPK)
   -> );
Query OK, 0 rows affected (0.46 sec)

親テーブルでは、列「StudentFKPK」が主キーとして機能します。次に、ALTERコマンドを使用して、子テーブル側に外部キーを追加していきます。

外部キーの追加方法

外部キーを追加するための基本構文は以下の通りです。

ALTER table yourChildTableName add constraint anyConstraintName
foreign key(primary key column name for parent table) 
references College(primary key column name for parent table);

この構文を実際のクエリに当てはめると、次のようになります。

mysql> alter table StudentEnrollment add constraint StudCollegeConst foreign key(StudentFKPK) references College(StudentFKPK);
Query OK, 0 rows affected (1.78 sec)
Records: 0  Duplicates: 0  Warnings: 0

これで、子テーブル「StudentEnrollment」の「StudentFKPK」列に対して、親テーブル「College」を参照する外部キー制約が設定されました。

外部キー制約の確認方法

外部キー制約が正しく設定されているかどうかは、INFORMATION_SCHEMA.KEY_COLUMN_USAGE テーブルを参照することで確認できます。確認用の構文は以下の通りです。

SELECT  TABLE_NAME, COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,
REFERENCED_COLUMN_NA FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHER  REFERENCED_TABLE_NAME = 'yourParentTableName or your referencedTableName';

上記の構文を実際のクエリに適用したものがこちらです。

mysql> SELECT
   ->   TABLE_NAME,
   ->   COLUMN_NAME,
   ->   CONSTRAINT_NAME,
   ->   REFERENCED_TABLE_NAME,
   ->   REFERENCED_COLUMN_NAME
   ->   FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
   ->   WHERE
   ->   REFERENCED_TABLE_NAME = 'College';

実行結果は以下のようになります。

+-------------------+-------------+------------------+-----------------------+------------------------+
| TABLE_NAME        | COLUMN_NAME | CONSTRAINT_NAME  | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+-------------------+-------------+------------------+-----------------------+------------------------+
| StudentEnrollment | StudentFKPK | StudCollegeConst | College               | StudentFKPK            |
+-------------------+-------------+------------------+-----------------------+------------------------+
1 row in set, 2 warnings (0.03 sec)

この結果から、子テーブル「StudentEnrollment」の列「StudentFKPK」に対して、制約名「StudCollegeConst」の外部キー制約が設定されており、親テーブル「College」の同名の列を参照していることが確認できます。