INFORMATION_SCHEMA ビューを使って外部キー情報を取得する方法

2024-04-02

MySQLで特定のテーブル.列を参照する外部キーを持つすべてのテーブルを見つける方法

必要なもの:

MySQLデータベース
MySQLクライアント (例: MySQL Workbench)

手順:

MySQLクライアントに接続します。
以下のSQLクエリを実行します。

SELECT
    t.table_name,
    c.column_name,
    r.referenced_table_name,
    r.referenced_column_name
FROM
    information_schema.referential_constraints AS r
INNER JOIN
    information_schema.tables AS t
ON
    r.table_name = t.table_name
INNER JOIN
    information_schema.columns AS c
ON
    r.column_name = c.column_name
WHERE
    r.referenced_table_name = '参照先のテーブル名'
    AND r.referenced_column_name = '参照先の列名';

クエリ解説:

information_schema.referential_constraints テーブル: 外部キーに関する情報を格納
INNER JOIN: 3つのテーブルを結合
WHERE 句: 特定のテーブル.列を参照する外部キーのみを抽出

例:

SELECT
    t.table_name,
    c.column_name,
    r.referenced_table_name,
    r.referenced_column_name
FROM
    information_schema.referential_constraints AS r
INNER JOIN
    information_schema.tables AS t
ON
    r.table_name = t.table_name
INNER JOIN
    information_schema.columns AS c
ON
    r.column_name = c.column_name
WHERE
    r.referenced_table_name = 'orders'
    AND r.referenced_column_name = 'customer_id';

このクエリは、orders テーブルの customer_id 列を参照する外部キーを持つすべてのテーブルとその列名を表示します。

補足:

このクエリは、information_schema データベース内のテーブルを使用します。
外部キー制約が設定されていない場合は、結果が空になります。
結果には、PRIMARY KEY 制約も含まれます。

-- サンプルデータベース
CREATE DATABASE IF NOT EXISTS sample_db;
USE sample_db;

-- テーブル作成
CREATE TABLE orders (
    order_id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT,
    ...
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    ...
);

-- 外部キー制約設定
ALTER TABLE orders
ADD FOREIGN KEY (customer_id)
REFERENCES customers (customer_id);

-- クエリ実行
SELECT
    t.table_name,
    c.column_name,
    r.referenced_table_name,
    r.referenced_column_name
FROM
    information_schema.referential_constraints AS r
INNER JOIN
    information_schema.tables AS t
ON
    r.table_name = t.table_name
INNER JOIN
    information_schema.columns AS c
ON
    r.column_name = c.column_name
WHERE
    r.referenced_table_name = 'orders'
    AND r.referenced_column_name = 'customer_id';

table_name | column_name | referenced_table_name | referenced_column_name
----------+-------------+----------------------+----------------------
orders     | customer_id | customers             | customer_id

このサンプルコードは、orders テーブルの customer_id 列を参照する外部キーを持つテーブル (orders) とその列名 (customer_id) を出力します。

特定のテーブル.列を参照する外部キーを持つすべてのテーブルを見つける方法

MySQL Workbench は、MySQL データベースを管理するための GUI ツールです。

MySQL Workbench でデータベースに接続します。
ナビゲーションパネルで、スキーマ > テーブル を展開します。
特定のテーブルを選択します。
テーブルの編集 タブを開きます。
外部キー タブを選択します。

このタブには、テーブルを参照する外部キーを持つすべてのテーブルとその列名が一覧表示されます。

以下の INFORMATION_SCHEMA ビューを使用できます。

REFERENTIAL_CONSTRAINTS: 外部キー制約に関する情報を表示
COLUMNS: 列に関する情報を表示
TABLES: テーブルに関する情報を表示

これらのビューを組み合わせて、特定のテーブル.列を参照する外部キーを持つすべてのテーブルを見つけることができます。

SELECT
    t.table_name,
    c.column_name
FROM
    information_schema.referential_constraints AS r
INNER JOIN
    information_schema.tables AS t
ON
    r.table_name = t.table_name
INNER JOIN
    information_schema.columns AS c
ON
    r.column_name = c.column_name
WHERE
    r.referenced_table_name = '参照先のテーブル名'
    AND r.referenced_column_name = '参照先の列名';

スクリプトを使用する

特定のテーブル.列を参照する外部キーを持つすべてのテーブルを見つけるスクリプトを作成できます。

import mysql.connector

# データベース接続
db = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="sample_db",
)

# カーソル取得
cursor = db.cursor()

# クエリ実行
cursor.execute("""
SELECT
    t.table_name,
    c.column_name
FROM
    information_schema.referential_constraints AS r
INNER JOIN
    information_schema.tables AS t
ON
    r.table_name = t.table_name
INNER JOIN
    information_schema.columns AS c
ON
    r.column_name = c.column_name
WHERE
    r.referenced_table_name = 'orders'
    AND r.referenced_column_name = 'customer_id';
""")

# 結果取得
for row in cursor.fetchall():
    print(row[0], row[1])

# クローズ
cursor.close()
db.close()