查看整个 MySQL 数据库的所有外键约束
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2684472/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
View all foreign key constraints for entire MySQL database
提问by Scott Wolf
I have a large database with over 150 tables that I've recently been handed. I'm just wondering if there is an easy way to view all foreign key constraints for the entire DB instead of on a per-table basis.
我有一个包含 150 多个表的大型数据库，我最近收到了这些表。我只是想知道是否有一种简单的方法可以查看整个数据库的所有外键约束，而不是在每个表的基础上。
回答by RedFilter
You can use the INFORMATION_SCHEMAtables for this. For example, the INFORMATION_SCHEMA TABLE_CONSTRAINTStable.
您可以INFORMATION_SCHEMA为此使用这些表格。例如，INFORMATION_SCHEMA TABLE_CONSTRAINTS表。
Something like this should do it:
像这样的事情应该这样做：
select *
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = 'FOREIGN KEY'
回答by genespos
This is what I prefer to get useful informations:
这是我更喜欢获取有用信息的方式：
SELECT CONSTRAINT_NAME,
UNIQUE_CONSTRAINT_NAME,
MATCH_OPTION,
UPDATE_RULE,
DELETE_RULE,
TABLE_NAME,
REFERENCED_TABLE_NAME
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'your_database_name'
回答by Buttle Butkus
The currently accepted answer by user RedFilter will work fine if you have just 1 database, but not if you have many.
如果您只有 1 个数据库，则用户 RedFilter 当前接受的答案可以正常工作，但如果您有多个数据库则不行。
After entering use information_schema;use this query to get foreign keys for name_of_db:
输入后use information_schema;使用此查询获取外键name_of_db：
select * from `table_constraints` where `table_schema` like `name_of_db` and `constraint_type` = 'FOREIGN KEY'
Use this query to get foreign keys for name_of_dbsaved to world-writeable file output_filepath_and_name:
使用此查询获取外键以name_of_db保存到世界可写文件output_filepath_and_name：
select * from `table_constraints` where `table_schema` like "name_of_db" and `constraint_type` = 'FOREIGN KEY' into outfile "output_filepath_and_name" FIELDS TERMINATED BY ',' ENCLOSED BY '"';
回答by Buttle Butkus
Query this code
查询此代码
select constraint_name,
table_schema,
table_name
from information_schema.table_constraints
You will get constraint_name, and filter the table_schema which is the list of database.
您将获得constraint_name，并过滤table_schema，它是database.
回答by Nisar
SQL:
查询语句：
select constraint_name,
table_schema,
table_name
from information_schema.table_constraints
where constraint_schema = 'astdb'
Output:
输出：
+----------------------------+--------------+---------------------+
| constraint_name | table_schema | table_name |
+----------------------------+--------------+---------------------+
| PRIMARY | astdb | asset_category |
| PRIMARY | astdb | asset_type |
| PRIMARY | astdb | asset_valuation |
| PRIMARY | astdb | assets |
| PRIMARY | astdb | com_mst |
| PRIMARY | astdb | com_typ |
| PRIMARY | astdb | ref_company_type |
| PRIMARY | astdb | supplier |
| PRIMARY | astdb | third_party_company |
| third_party_company_ibfk_1 | astdb | third_party_company |
| PRIMARY | astdb | user |
| PRIMARY | astdb | user_role |
+----------------------------+--------------+---------------------+
