How to List foreign keys in Mysql

information_schema

The following query will list out the foreign keys in mysql. It finds it out from the information_schema database.

select 
    concat(table_name, '.', column_name) as 'foreign key',  
    concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null;

The output is a clean table listing out all foreign keys from all databases

+-----------------------+-------------+
| foreign key           | references  |
+-----------------------+-------------+
| orders.client_id      | clients.id  |
| line_items.order_id   | orders.id   |
| line_items.product_id | products.id |
+-----------------------+-------------+

Source
http://www.conandalton.net/2008/09/list-foreign-key-constraints-in-oracle.html

To view the foreign keys of only a particular database use WHERE table_schema = 'database_name'

select 
    concat(table_name, '.', column_name) as 'foreign key',  
    concat(referenced_table_name, '.', referenced_column_name) as 'references'
from
    information_schema.key_column_usage
where
    referenced_table_name is not null
    and table_schema = 'my_database'

Basically the key_column_usage table contains information about all keys in all available databases. So it can be queried in any way to get the desired information.

show create table

Another way would be to show the table structure.

SHOW CREATE TABLE tablename;

It displays the sql query to create the table and has the foreign key constraints too.

phpmyadmin

The "Relation View" in phpmyadmin shows all the foreign key constraints.

Resources

1. http://dev.mysql.com/doc/refman/5.1/en/innodb-foreign-key-constraints.html