View all foreign keys to a table or column with MySQL

Posted on May 19, 2017 at 5:23 pm

View all foreign keys to 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 = 'dbname' AND
  REFERENCED_TABLE_NAME = 'tablename';

View all foreign keys to 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 = 'dbname' AND
  REFERENCED_COLUMN_NAME = 'columnname';

Another quick way is to use:

SHOW CREATE TABLE `tablename`;

Other Posts

Updated Posts