MySQLで各制約のフィールドを取得する方法｜information_schema.key_column_usageの使い方

複数のテーブルを持つデータベース「business」を例に考えてみましょう。各制約（CONSTRAINT）にどのフィールド（カラム）が含まれているのかを確認したい場合は、information_schema.key_column_usage テーブルを参照するのが便利です。

各制約のフィールドを取得するSQL

以下のクエリを実行すると、「business」スキーマに定義されたすべての制約と、それぞれに関連付けられたカラム情報を一覧で取得できます。

mysql> select *
    -> from information_schema.key_column_usage
    -> where constraint_schema = 'business';

実行結果

クエリを実行すると、次のような結果が返されます（以下の出力は抜粋です。実際には52行が表示されます）。

+--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME          | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME                   | COLUMN_NAME  | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
| def                | business          | PRIMARY                  | def           | business     | primarytable                 | FKPK         | 1                | NULL                          | NULL                    | NULL                  | NULL                   |
| def                | business          | PRIMARY                  | def           | business     | primarytable1                | Fk_pk        | 1                | NULL                          | NULL                    | NULL                  | NULL                   |
| def                | business          | PRIMARY                  | def           | business     | autoincrementtable           | id           | 1                | NULL                          | NULL                    | NULL                  | NULL                   |
| def                | business          | name                     | def           | business     | uniquedemo                   | name         | 1                | NULL                          | NULL                    | NULL                  | NULL                   |
| def                | business          | id                       | def           | business     | uniquedemo1                  | id           | 1                | NULL                          | NULL                    | NULL                  | NULL                   |
| def                | business          | id                       | def           | business     | uniquedemo1                  | name         | 2                | NULL                          | NULL                    | NULL                  | NULL                   |
| def                | business          | constFKPK                | def           | business     | foreigntable                 | Fk_pk        | 1                | 1                             | business                | primarytable1         | fk_pk                  |
| def                | business          | FKConst                  | def           | business     | foreigntabledemo             | FK           | 1                | 1                             | business                | primarytabledemo      | fk                     |
| def                | business          | primarytable1demo_ibfk_1 | def           | business     | primarytable1demo            | ForeignId    | 1                | 1                             | business                | foreigntable1         | studentid              |
+--------------------+-------------------+--------------------------+---------------+--------------+------------------------------+--------------+------------------+-------------------------------+-------------------------+-----------------------+------------------------+
52 rows in set, 2 warnings (0.21 sec)

結果の見方（主なカラムの意味）

• CONSTRAINT_SCHEMA： 制約が属するデータベース（スキーマ）名
• CONSTRAINT_NAME： 制約名。「PRIMARY」は主キー制約を表します
• TABLE_NAME： 制約が設定されているテーブル名
• COLUMN_NAME： 制約の対象となるカラム名
• ORDINAL_POSITION： 複合キーの場合におけるカラムの順序（1始まり）
• POSITION_IN_UNIQUE_CONSTRAINT： 外部キー制約の場合、参照先ユニークキー内での位置
• REFERENCED_TABLE_NAME／REFERENCED_COLUMN_NAME： 外部キー制約において参照されるテーブル名とカラム名

結果を見ると、主キーや一意キーの制約では参照先の情報がNULLになっている一方、外部キー制約（constFKPK、FKConst、primarytable1demo_ibfk_1など）では、参照元テーブルと参照先テーブル・カラムの対応関係を確認できます。

スキーマ設計やデバッグの際に、データベース内の制約の全体像を把握したいときに、このクエリをぜひ活用してください。