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.

Taler

See Andy's answer here: #202121 – Packet

Answer 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'

Staffard

Looks like that has exactly what I need. Thanks! – Taler

Is there any way to actually list the field name of the foreign key too? – Charbonneau

Answer 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'

Shroyer

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

Poster

Answer 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

Nickens

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

Claudeclaudel

Answer 6

SELECT RefCons.constraint_schema, RefCons.table_name, RefCons.referenced_table_name, RefCons.constraint_name, KeyCol.column_name, KeyCol.referenced_column_name
FROM information_schema.referential_constraints RefCons
JOIN information_schema.key_column_usage KeyCol ON RefCons.constraint_schema = KeyCol.table_schema
     AND RefCons.table_name = KeyCol.table_name
     AND RefCons.constraint_name = KeyCol.constraint_name
WHERE RefCons.constraint_schema = 'DATABASE_NAME';

Cruz