View all foreign key constraints for entire MySQL database

Question

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.

See Andy's answer here: https://stackoverflow.com/questions/201621/how-do-i-see-all-foreign-keys-to-a-table-or-column — omarjebari, Aug 09 '17 at 18:32

D'Arcy Rittich · Accepted Answer · 2010-04-21T16:00:33.133

94

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'

edited Apr 21 '10 at 16:00

answered Apr 21 '10 at 15:55

D'Arcy Rittich

167,292
40
290
283

Looks like that has exactly what I need. Thanks! – Scott Wolf Apr 21 '10 at 15:58
6

Is there any way to actually list the field name of the foreign key too? – JoeTidee Aug 18 '15 at 08:47

score 26 · Answer 2 · answered Apr 18 '16 at 07:44

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'

Buttle Butkus · Answer 3 · 2017-07-21T22:09:58.253

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 '"';

score 2 · Answer 4 · answered May 25 '14 at 08:58

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

score 1 · Answer 5 · edited Apr 19 '14 at 02:09

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

View all foreign key constraints for entire MySQL database

5 Answers5

Linked