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






Last Updated On : 5th September 2009

Related Post

5 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 to alex Cancel reply

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