6
Help us understand the problem. What are the problem?

More than 1 year has passed since last update.

posted at

MySQLでinformation_schemaから外部キー参照を確認する時のメモ

外部キーの一覧を見たい、でもテーブルの定義を1個1個見ていくのは面倒。
そんな時にinformation_schemaから制約を取得できるので自分用メモ。

table_constraints

MySQLで外部キーをはっているテーブルを調査するのに使える

select * from `information_schema`.table_constraints
where
table_schema = "{スキーマ名}" and
constraint_type="FOREIGN KEY"
;

key_column_usage

特定のテーブルへの外部キーをはってあるテーブルの一覧を取得できる

select * from information_schema.key_column_usage
where
  constraint_schema='{スキーマ名}' and
  referenced_table_name='table_name'
;
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Sign upLogin
6
Help us understand the problem. What are the problem?