MySQL Foreign Keys

gistfile1.md

There are several ways to list foreign keys in MySQL.

Firstly:

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;

will produce a clean table as follows:

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

Alternatively we can use SHOW CREATE TABLE tablename; which will give us the SQL to create the table, including setting up the foreign key relations.

Also, if using phpMyAdmin, the "Relation View" will show the foreign key constraints as well.