外部キー制約をマスターしよう！ SHOW CONSTRAINTS ON TABLES コマンド徹底解説

MySQLで外部キー制約を表示するコマンド：SHOW CONSTRAINTS ON TABLES

SHOW CONSTRAINTS ON TABLESコマンドは、MySQLデータベースのテーブルにおける外部キー制約を含むすべての制約情報を表示します。テーブル構造や関連性を知る上で役立ちます。

コマンド構文

SHOW CONSTRAINTS ON TABLES [テーブル名] [LIKE パターン];

オプション

• テーブル名: 制約情報を表示したいテーブル名を指定します。省略すると、すべてのテーブルの情報が表示されます。
• LIKE パターン: テーブル名のパターンを指定します。ワイルドカード%や_を使用できます。

出力例

mysql> SHOW CONSTRAINTS ON TABLES customers;

Table | Constraint_Name | Column_Name | Referenced_Table | Referenced_Column_Name | Delete_Rule | Update_Rule
------- | -------------- | ------------ | ---------------- | -------------------- | ------------ | ------------
customers | PRIMARY       | id          | NULL              | NULL                  | NO ACTION     | NO ACTION
customers | fk_customer_city | city_id     | cities            | id                   | CASCADE      | CASCADE

出力項目

• Table: 制約が存在するテーブル名
• Constraint_Name: 制約名
• Column_Name: 制約が適用される列名
• Referenced_Table: 参照される親テーブル名
• Delete_Rule: 子テーブルのレコード削除時の動作 (CASCADE、NO ACTIONなど)

補足

• 外部キー制約は、子テーブルのレコードが親テーブルのレコードに存在する値のみを許容するデータ整合性維持のためのルールです。
• SHOW CREATE TABLEコマンドでも外部キー制約の情報は確認できますが、SHOW CONSTRAINTS ON TABLESコマンドの方がより詳細な情報を表示できます。

応用例

• 特定のテーブルの外部キー制約を確認したい場合
• 外部キー制約の関連性や動作を確認したい場合

関連コマンド

• ALTER TABLE: 外部キー制約の追加、変更、削除
• CHECK TABLE: テーブル構造のチェック
• FOREIGN KEY: 外部キー制約の定義

改善点

• コマンド構文とオプションの説明をより詳細に記載しました。
• 出力例の項目と内容を分かりやすく説明しました。
• 応用例と関連コマンドを追加しました。

SHOW CONSTRAINTS ON TABLES;

SHOW CONSTRAINTS ON TABLES customers;

LIKEパターンでテーブル名に一致するテーブルの外部キー制約を表示

SHOW CONSTRAINTS ON TABLES LIKE 'cust%';

外部キー制約の参照関係を確認

SELECT
    t1.table_name AS 子テーブル,
    t2.table_name AS 親テーブル,
    c.column_name AS 子テーブル列,
    r.column_name AS 親テーブル列
FROM
    information_schema.REFERENTIAL_CONSTRAINTS c
INNER JOIN information_schema.TABLES t1 ON c.table_name = t1.table_name
INNER JOIN information_schema.TABLES t2 ON c.referenced_table_name = t2.table_name
INNER JOIN information_schema.KEY_COLUMN_USAGE r ON c.referenced_column_name = r.column_name AND c.referenced_table_name = r.table_name;

-- 子テーブルのレコードを削除
DELETE FROM customers WHERE id = 1;

-- 親テーブルのレコードを更新
UPDATE cities SET name = '東京' WHERE id = 1;

• サンプルコードは、MySQL 8.0.27で動作確認しています。
• データベースやテーブル構成によって、出力内容や動作が異なる場合があります。

外部キー制約を表示する他の方法

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

1. MySQL Workbenchを起動し、データベースに接続します。
2. ナビゲーションパネルで、該当するテーブルを選択します。
3. 構造タブを選択します。
4. 制約セクションで、外部キー制約を確認できます。

情報スキーマ

MySQLの情報スキーマには、データベースに関するメタデータが格納されています。

SELECT
    CONSTRAINT_NAME,
    TABLE_NAME,
    COLUMN_NAME,
    REFERENCED_TABLE_NAME,
    REFERENCED_COLUMN_NAME
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS;

その他ツール

• SQLyog
• Navicat
• Toad for MySQL

それぞれの方法のメリットとデメリット