[MySQL]特定のテーブル・カラムを外部参照しているテーブルの一覧を取得する方法 #MySQL

概要

普段の頻度はそんなに多くはないかもしれませんが、データベースを扱っていてときどき「 xxx.id を外部参照しているテーブルをリストアップしたい」という場面があります。(ないという方はすみません)

そんなとき、下記のコマンドで調査が可能です。

USE information_schema;

SELECT
  TABLE_NAME, --- 参照している側のテーブル名が表示される
  COLUMN_NAME --- 参照している側のカラム名が表示される
FROM
  KEY_COLUMN_USAGE
WHERE
  CONSTRAINT_SCHEMA = 'DB名'
  AND REFERENCED_TABLE_NAME = '参照されている側のテーブル名'
  AND REFERENCED_COLUMN_NAME = '参照されている側のカラム名'
;

サンプル

実際にやってみましょう。

例えばこんなDBがあります。DB名は test とします。
diaries テーブルと avatars テーブルから users.id を参照しているという点だけ把握していただければ、細かいところは読み飛ばしてもらって大丈夫ですｗ

ER図

CREATE文

一応のせます。

CREATE TABLE users (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50),
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE diaries (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int(10) unsigned NOT NULL,
  `title` VARCHAR(255),
  `body` TEXT,
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `diaries_fk1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE avatars (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `user_id` int(10) unsigned NOT NULL,
  `image_path` VARCHAR(255),
  `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT `avatars_fk1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
);

コマンド実行結果

ここで、users.id を外部参照しているテーブルを確認したい、という場合にこんなかんじで使います。

mysql> USE information_schema;
Database changed

mysql> SELECT
    ->   TABLE_NAME,
    ->   COLUMN_NAME
    -> FROM
    ->   KEY_COLUMN_USAGE
    -> WHERE
    ->   CONSTRAINT_SCHEMA = 'test'
    ->   AND REFERENCED_TABLE_NAME = 'users'
    ->   AND REFERENCED_COLUMN_NAME = 'id'
    -> ;
+------------+-------------+
| TABLE_NAME | COLUMN_NAME |
+------------+-------------+
| avatars    | user_id     |
| diaries    | user_id     |
+------------+-------------+
2 rows in set (0.46 sec)

users.id を参照しているテーブルとそのカラムがコマンドの実行結果からわかります。

わりと便利なので、もしご存知なかったかたは試してみてください

環境

MySQL のバージョンは 5.6.31 で確認しています。

参考

INFORMATION_SCHEMA KEY_COLUMN_USAGE

おまけ

例えば、こんな風にすると、users.id を外部参照してて、かつ ON DELETE CASCADE になっていないテーブルをリストアップできたりします。

SELECT TABLE_NAME, REFERENCED_TABLE_NAME, DELETE_RULE FROM referential_constraints
WHERE TABLE_NAME IN (
  SELECT
    TABLE_NAME
  FROM
    key_column_usage
  WHERE
    CONSTRAINT_SCHEMA='test'
    AND REFERENCED_TABLE_NAME='users'
    AND REFERENCED_COLUMN_NAME='id'
)
AND CONSTRAINT_SCHEMA = 'test'
AND REFERENCED_TABLE_NAME = 'users'
AND DELETE_RULE != 'CASCADE'

もし ON DELETE CASCADE になっていないものがヒットした場合は例えばこんな風にでます。前述の avatars の CREATE TABLE 表記を見ていただくとわかりますが、ON DELETE の設定をしていません。そのため RESTRICT となっていることがわかります。

+------------+-----------------------+-------------+
| TABLE_NAME | REFERENCED_TABLE_NAME | DELETE_RULE |
+------------+-----------------------+-------------+
| avatars    | users                 | RESTRICT    |
+------------+-----------------------+-------------+
1 row in set (0.03 sec)

REFERENTIAL_CONSTRAINTS テーブル