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>