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

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

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

```sql
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図
![ertest.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/70638/b8d3d432-4088-07ae-6a90-df4b7a9b2d1a.png)


### CREATE文
一応のせます。

```sql
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` を外部参照しているテーブルを確認したい、という場合にこんなかんじで使います。

```sql
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` を参照しているテーブルとそのカラムがコマンドの実行結果からわかります。

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

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

## 参考
INFORMATION_SCHEMA KEY_COLUMN_USAGE

https://dev.mysql.com/doc/refman/5.6/ja/information-schema-key-column-usage-table.html

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

```sql
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 テーブル

https://dev.mysql.com/doc/refman/5.6/ja/information-schema-referential-constraints-table.html