MySQLでテーブルやカラムに設定されたすべての外部キーを確認する方法

MySQLでは、特定のテーブルやカラムに張られたすべての外部キー（FOREIGN KEY）を確認するには、INFORMATION_SCHEMA.KEY_COLUMN_USAGE を参照し、REFERENCED_TABLE_NAME や REFERENCED_COLUMN_NAME の値で絞り込むのが一般的な方法です。

準備：サンプルテーブルを作成する

まず、外部キーの関係を持つ2つのテーブルを作成します。最初に、外部キーを保持する側のテーブル「ForeignTable」を作成しましょう。

mysql> CREATE table ForeignTable
-> (
-> id int,
-> name varchar(200),
-> Fk_pk int
-> );
Query OK, 0 rows affected (0.43 sec)

次に、主キーを持つ参照先テーブル「primaryTable1」を作成します。

mysql> CREATE table primaryTable1
-> (
-> Fk_pk int,
-> DeptName varchar(200),
-> Primary key(Fk_pk)
-> );
Query OK, 0 rows affected (0.48 sec)

ALTER TABLEで外部キー制約を追加する

両方のテーブルが作成できたら、ALTER TABLE コマンドを使って外部キー制約を追加します。基本的な構文は以下の通りです。

alter table yourFirstTable add constraint anyConstraintName foreign key(column_name which is
acts foreign key in second table) yourSecondTable(column_name which acts primary key in
second table).

この構文を実際に適用して、2つのテーブルを関連付けます。

mysql> alter table ForeignTable add constraint constFKPK foreign key(Fk_pk) references
primaryTable1(Fk_pk);
Query OK, 0 rows affected (1.57 sec)
Records: 0 Duplicates: 0 Warnings: 0

これで「ForeignTable」の Fk_pk カラムが、「primaryTable1」の主キー Fk_pk を参照する外部キーとして登録されました。

テーブルへの外部キーを一覧表示するクエリ

あるテーブルに対して設定されたすべての外部キーを確認するには、次の構文を使用します。

SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME,
REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME = 'yourReferencedTableName';

実際に、参照先テーブル「primarytable1」に対する外部キーを検索してみましょう。

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

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

+--------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME   | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------+-------------+-----------------+-----------------------+------------------------+
| foreigntable | Fk_pk       | constFKPK       | primarytable1         | fk_pk                  |
+--------------+-------------+-----------------+-----------------------+------------------------+
1 row in set, 2 warnings (0.02 sec)

この出力からは、制約名（CONSTRAINT_NAME）が「constFKPK」であり、外部キーを持つテーブル名（TABLE_NAME）が「foreigntable」であることが分かります。

特定のカラムへの外部キーを一覧表示するクエリ

続いて、特定のカラムに向けられた外部キーだけを抽出したい場合は、データベース名とカラム名も条件に加えます。

SELECT
TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
REFERENCED_TABLE_SCHEMA = 'yourDatabaseName' AND
REFERENCED_TABLE_NAME = 'yourreferencedtablename' AND
REFERENCED_COLUMN_NAME = 'yourreferencedcolumnname';

例えば、データベース「business」内のテーブル「primarytable1」のカラム「fk_pk」を参照している外部キーを調べるクエリは次の通りです。

mysql> SELECT
-> TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,
REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
-> FROM
-> INFORMATION_SCHEMA.KEY_COLUMN_USAGE
-> WHERE
-> REFERENCED_TABLE_SCHEMA = 'business' AND
-> REFERENCED_TABLE_NAME = 'primarytable1' AND REFERENCED_COLUMN_NAME
= 'fk_pk';

実行結果：

+--------------+-------------+-----------------+-----------------------+------------------------+
| TABLE_NAME   | COLUMN_NAME | CONSTRAINT_NAME | REFERENCED_TABLE_NAME |REFERENCED_COLUMN_NAME  |
+--------------+-------------+-----------------+-----------------------+------------------------+
| foreigntable | Fk_pk       | constFKPK       | primarytable1         | fk_pk                  |
+--------------+-------------+-----------------+-----------------------+------------------------+
1 row in set, 2 warnings (0.03 sec)

まとめ

INFORMATION_SCHEMA.KEY_COLUMN_USAGE テーブルを利用することで、テーブル単位でもカラム単位でも、参照されている外部キーの情報（テーブル名・カラム名・制約名など）を簡単に一覧表示できます。データベース設計の見直しや、既存のリレーションシップの調査にぜひ活用してください。