List foreign keys in mysql

By | September 5, 2009

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

About Silver Moon

A Tech Enthusiast, Blogger, Linux Fan and a Software Developer. Writes about Computer hardware, Linux and Open Source software and coding in Python, Php and Javascript. He can be reached at [email protected].

6 thoughts on “List foreign keys in mysql

  1. Doesn't work

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

    Can your provide the schema definition ?

Leave a Reply

Your email address will not be published. Required fields are marked *