List foreign keys in mysql

An article by Binary Tides    4 Comments

One interesting way of listing foreign keys is like this :

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;

which is found here

and gives an output like this :

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

Another way would be to :

SHOW CREATE TABLE tablename;

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

Apart from this phpmyadmin too shows the foreign keys on the top of a table structure.

References :
1. Mysql Docs

Popularity: 17% [?]