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







Last Updated On : 5th September 2009

Subscribe to get updates delivered to your inbox

5 Comments + Add Comment

  • i ‘m very appreciated

  • The show create table command is great.. too bad describe [table] doesn’t provide more information about keys and such.

  • Thank you, I’ve tried the first solution and it worked perfectly.

  • Thanks a lot!!! Super!!! Finally someone posted a real solution.

  • It doesn’t work. Because the referenced_table_name IS NULL. Is this a MySQL bug ?

    Can your provide the schema definition ?

Leave a comment