View all foreign key constraints for entire MySQL database

mysql foreign-keys database
Scott Wolf picture Scott Wolf · Apr 21, 2010 · Viewed 35.7k times · Source

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.

Answer

D'Arcy Rittich picture D'Arcy Rittich · Apr 21, 2010

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'

Related questions

Can table columns with a Foreign Key be NULL?

I have a table which has several ID columns to other tables. I want a foreign key to force integrity only if I put data in there. If I do an update at a later time to populate that column, …

Read More
Meaning of "n:m" and "1:n" in database design

In database design what do n:m and 1:n mean? Does it have anything to do with keys or relationships?

Read More
Foreign key constraints: When to use ON UPDATE and ON DELETE

I'm designing my database schema using MySQL Workbench, which is pretty cool because you can do diagrams and it converts them :P Anyways, I've decided to use InnoDB because of it's Foreign Key support. One thing I noticed though is …

Read More