Tag Archives: Foreign Key

RDBMSを運用していて、Queryでオブジェクトの確認をしたい場合がありますが、
information_schema.table_constraintsでは外部キーなどのオブジェクト依存関係を
確認する事が出来ます。

mysql> select @@version;
+------------+
| @@version  |
+------------+
| 5.6.19-log |
+------------+
1 row in set (0.00 sec)

mysql>

特定データベースにてFK制約を確認する方法。

mysql> SELECT * FROM information_schema.table_constraints
    -> WHERE table_schema = 'sakila'
    -> AND constraint_type='FOREIGN KEY';
+--------------------+-------------------+---------------------------+--------------+---------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME           | TABLE_SCHEMA | TABLE_NAME    | CONSTRAINT_TYPE |
+--------------------+-------------------+---------------------------+--------------+---------------+-----------------+
| def                | sakila            | fk_address_city           | sakila       | address       | FOREIGN KEY     |
| def                | sakila            | fk_city_country           | sakila       | city          | FOREIGN KEY     |
| def                | sakila            | fk_customer_address       | sakila       | customer      | FOREIGN KEY     |
| def                | sakila            | fk_customer_store         | sakila       | customer      | FOREIGN KEY     |
| def                | sakila            | fk_film_language          | sakila       | film          | FOREIGN KEY     |
| def                | sakila            | fk_film_language_original | sakila       | film          | FOREIGN KEY     |
| def                | sakila            | fk_film_actor_actor       | sakila       | film_actor    | FOREIGN KEY     |
| def                | sakila            | fk_film_actor_film        | sakila       | film_actor    | FOREIGN KEY     |
| def                | sakila            | fk_film_category_category | sakila       | film_category | FOREIGN KEY     |
| def                | sakila            | fk_film_category_film     | sakila       | film_category | FOREIGN KEY     |
| def                | sakila            | fk_inventory_film         | sakila       | inventory     | FOREIGN KEY     |
| def                | sakila            | fk_inventory_store        | sakila       | inventory     | FOREIGN KEY     |
| def                | sakila            | fk_payment_customer       | sakila       | payment       | FOREIGN KEY     |
| def                | sakila            | fk_payment_rental         | sakila       | payment       | FOREIGN KEY     |
| def                | sakila            | fk_payment_staff          | sakila       | payment       | FOREIGN KEY     |
| def                | sakila            | fk_rental_customer        | sakila       | rental        | FOREIGN KEY     |
| def                | sakila            | fk_rental_inventory       | sakila       | rental        | FOREIGN KEY     |
| def                | sakila            | fk_rental_staff           | sakila       | rental        | FOREIGN KEY     |
| def                | sakila            | fk_staff_address          | sakila       | staff         | FOREIGN KEY     |
| def                | sakila            | fk_staff_store            | sakila       | staff         | FOREIGN KEY     |
| def                | sakila            | fk_store_address          | sakila       | store         | FOREIGN KEY     |
| def                | sakila            | fk_store_staff            | sakila       | store         | FOREIGN KEY     |
+--------------------+-------------------+---------------------------+--------------+---------------+-----------------+
22 rows in set (0.00 sec)

特定のテーブルの外部キー制約のみ確認

mysql> SELECT * FROM information_schema.table_constraints
    -> WHERE table_schema = 'sakila'
    -> AND table_name = 'customer'
    -> AND constraint_type='FOREIGN KEY';
+--------------------+-------------------+---------------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME     | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+---------------------+--------------+------------+-----------------+
| def                | sakila            | fk_customer_address | sakila       | customer   | FOREIGN KEY     |
| def                | sakila            | fk_customer_store   | sakila       | customer   | FOREIGN KEY     |
+--------------------+-------------------+---------------------+--------------+------------+-----------------+
2 rows in set (0.00 sec)

特定のテーブルの制約を確認

mysql> SELECT * FROM information_schema.table_constraints
    -> WHERE table_schema = 'sakila'
    -> AND table_name = 'customer';
+--------------------+-------------------+---------------------+--------------+------------+-----------------+
| CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME     | TABLE_SCHEMA | TABLE_NAME | CONSTRAINT_TYPE |
+--------------------+-------------------+---------------------+--------------+------------+-----------------+
| def                | sakila            | PRIMARY             | sakila       | customer   | PRIMARY KEY     |
| def                | sakila            | fk_customer_address | sakila       | customer   | FOREIGN KEY     |
| def                | sakila            | fk_customer_store   | sakila       | customer   | FOREIGN KEY     |
+--------------------+-------------------+---------------------+--------------+------------+-----------------+
3 rows in set (0.00 sec)

mysql>