View all foreign key constraints for entire MySQL

问题:

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.

回答1:

You can use the INFORMATION_SCHEMA tables for this. For example, the INFORMATION_SCHEMA TABLE_CONSTRAINTS table.

Something like this should do it:

select *
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where CONSTRAINT_TYPE = 'FOREIGN KEY'

回答2:

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'

回答3:

The currently accepted answer by user RedFilter will work fine if you have just 1 database, but not if you have many.

After entering use information_schema; use this query to get foreign keys for 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_db saved to world-writeable file 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 '"';

回答4:

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.

Look at This

回答5:

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           |
+----------------------------+--------------+---------------------+