CODEWITHSUNDEEP
mysqlforeign-keysdatabase
Scott Wolf
Scott Wolf

Reputation: 803

View all foreign key constraints for entire MySQL database

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.

Upvotes: 65

Views: 49197

Answers (7)

Eslam Badawy
Eslam Badawy

Reputation: 529

to have full information about foreign keys you can use this:

set @db = 'mydb';
SELECT DISTINCT KEY_COLUMN_USAGE.CONSTRAINT_NAME, KEY_COLUMN_USAGE.TABLE_NAME, KEY_COLUMN_USAGE.COLUMN_NAME, KEY_COLUMN_USAGE.REFERENCED_TABLE_NAME, KEY_COLUMN_USAGE.REFERENCED_COLUMN_NAME ,REFERENTIAL_CONSTRAINTS.UPDATE_RULE, REFERENTIAL_CONSTRAINTS.DELETE_RULE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
ON TABLE_CONSTRAINTS.CONSTRAINT_NAME=KEY_COLUMN_USAGE.CONSTRAINT_NAME AND TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA=KEY_COLUMN_USAGE.CONSTRAINT_SCHEMA
LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
ON TABLE_CONSTRAINTS.CONSTRAINT_NAME=REFERENTIAL_CONSTRAINTS.CONSTRAINT_NAME AND TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA=REFERENTIAL_CONSTRAINTS.CONSTRAINT_SCHEMA
    WHERE TABLE_CONSTRAINTS.CONSTRAINT_TYPE="FOREIGN KEY" AND TABLE_CONSTRAINTS.CONSTRAINT_SCHEMA = @db;

output: enter image description here

Upvotes: 0

live-love
live-love

Reputation: 52366

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

Upvotes: 0

Buttle Butkus
Buttle Butkus

Reputation: 9466

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

Upvotes: 7

genespos
genespos

Reputation: 3311

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'

Upvotes: 28

user3673263
user3673263

Reputation:

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

Upvotes: 2

Nisar
Nisar

Reputation: 6038

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

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171421

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'

Upvotes: 96

Related Questions