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

by

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.

More Related Contents:

  1. MySQL foreign key constraints, cascade delete
  2. MySQL InnoDB foreign key between different databases
  3. Force InnoDB to recheck foreign keys on a table/tables?
  4. MySQL DROP all tables, ignoring foreign keys
  5. MySQL Creating tables with Foreign Keys giving errno: 150
  6. MyISAM versus InnoDB [closed]
  7. MySQL Error 1215: Cannot add foreign key constraint
  8. How do I quickly rename a MySQL database (change schema name)?
  9. MySQL Cannot Add Foreign Key Constraint
  10. How to find all tables that have foreign keys that reference particular table.column and have values for those foreign keys?
  11. TINYTEXT, TEXT, MEDIUMTEXT, and LONGTEXT maximum storage sizes
  12. Howto: Clean a mysql InnoDB storage engine?
  13. MySQL InnoDB not releasing disk space after deleting data rows from table
  14. MySQL – Conditional Foreign Key Constraints
  15. Why is MySQL InnoDB insert so slow?
  16. Add Foreign Key to existing table
  17. How to change the foreign key referential action? (behavior)
  18. Prevent auto increment on MySQL duplicate insert
  19. How to truncate a foreign key constrained table?
  20. Bogus foreign key constraint fail
  21. How to optimize COUNT(*) performance on InnoDB by using index
  22. Show constraints on tables command
  23. MySQL Errno 150
  24. How to remove constraints from my MySQL table?
  25. Database corruption with MariaDB : Table doesn’t exist in engine
  26. Defining Composite Key with Auto Increment in MySQL
  27. MySQL “ERROR 1005 (HY000): Can’t create table ‘foo.#sql-12c_4’ (errno: 150)”
  28. MySQL errorno 121
  29. MySQL Relationships
  30. How can I rebuild indexes and update stats in MySQL innoDB?

Leave a Comment