【MySQL】外部キーやユニークキーなどのTable情報を確認する方法
やること
MySQLのテーブル情報を詳細に確認する方法を紹介します。descコマンド場合、カラム名等は取得できますが、以下の情報は詳細に取得できません。
- 外部キー制約(CASCADE, restrict等含め)
- ユニークキー制約
解決法
SHOW CREATE TABLE table_name
を実行しましょう。
例
mysql> desc test_table;
+------------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+----------------+
| id | bigint | NO | PRI | NULL | auto_increment |
| user_id | bigint | NO | MUL | NULL | |
| name | text | NO | | NULL | |
| created_at | datetime(6) | NO | | NULL | |
| updated_at | datetime(6) | NO | | NULL | |
+------------+-------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)
mysql> SHOW CREATE TABLE test_table;
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test_table | CREATE TABLE `test_table` (
`id` bigint NOT NULL AUTO_INCREMENT,
`user_id` bigint NOT NULL,
`name` text NOT NULL,
`created_at` datetime(6) NOT NULL,
`updated_at` datetime(6) NOT NULL,
PRIMARY KEY (`id`),
KEY `index_test_table_on_user_id` (`user_id`),
CONSTRAINT `fk_test_0e2d2c3db31` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci |
+----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)