How do I see all foreign keys to a table or column?

mysql foreign-keys innodb
Christian Oudard picture Christian Oudard · Oct 14, 2008 · Viewed 453.1k times · Source

In MySQL, how do I get a list of all foreign key constraints pointing to a particular table? a particular column? This is the same thing as this Oracle question, but for MySQL.

Answer

Vinko Vrsalovic picture Vinko Vrsalovic · Oct 14, 2008

For a Table:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>';

For a Column:

SELECT 
  TABLE_NAME,COLUMN_NAME,CONSTRAINT_NAME, REFERENCED_TABLE_NAME,REFERENCED_COLUMN_NAME
FROM
  INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
  REFERENCED_TABLE_SCHEMA = '<database>' AND
  REFERENCED_TABLE_NAME = '<table>' AND
  REFERENCED_COLUMN_NAME = '<column>';

Basically, we changed REFERENCED_TABLE_NAME with REFERENCED_COLUMN_NAME in the where clause.

Related questions

MySQL DROP all tables, ignoring foreign keys

Is there a nice easy way to drop all tables from a MySQL database, ignoring any foreign key constraints that may be in there?

Read More
MySQL foreign key constraints, cascade delete

I want to use foreign keys to keep the integrity and avoid orphans (I already use innoDB). How do I make a SQL statment that DELETE ON CASCADE? If I delete a category then how do I make sure that …

Read More
Why to use foreign keys with no action on delete or update

I have a question of interest: I have 2 tables in mysql with InnoDb. table tbl_a has a primary key, named a_id; table tbl_b has a primary b_id and a foreign key on tbl_a.a_id …

Read More