mysql查詢某張表的外鍵

標籤：

mysql和oracle一樣也是有資料字典表的，是存在單獨的一個庫叫INFORMATION_SCHEMA，要查看某張表的外鍵要從字典表中尋找

如mysql查詢某張表的外鍵，可以用下面的方式查詢

use INFORMATION_SCHEMA;

select TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME,REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME from KEY_COLUMN_USAGE where table_name = ‘logistics_member_express‘;

+--------------------------+-----------------+----------------------------+------------------------+------------------------+

| TABLE_NAME               | COLUMN_NAME     | CONSTRAINT_NAME            | REFERENCED_TABLE_NAME  | REFERENCED_COLUMN_NAME |

+--------------------------+-----------------+----------------------------+------------------------+------------------------+

| logistics_member_express | id              | PRIMARY                    | NULL                   | NULL                   |

| logistics_member_express | express_user_id | Reflogistics_express_user6 | logistics_express_user | id                     |

+--------------------------+-----------------+----------------------------+------------------------+------------------------+

2 rows in set (0.00 sec)

如果某個外鍵不要的話就可以刪除啦：

ALTER TABLE logistics_member_express DROP FOREIGN KEY  Reflogistics_express_user6;

mysql查詢某張表的外鍵