MySQL: How do I find out which tables reference a specific table?

Question

I want to drop a table but it is referenced by one or more other tables. How can I find out which tables are referencing this table without having to look at each of the tables in the database one by one?

Answer 1

SELECT TABLE_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_schema_name'
      AND REFERENCED_TABLE_NAME = 'your_table_name';

This works.

Answer 2

select table_name 
from information_schema.referential_constraints 
where referenced_table_name = 'parent table here';

Answer 3

If you have phpMyAdmin installed, you can use its designer feature to visualize table relationships.

To use the designer, select a database, then look for the Designer tab.

Answer 4

you could try MySql workbench which allows you to extract E.R. diagram. In this you can find all you need about tables of your database.

Answer 5

If you also want the specific column where the references are made use the following query.

SELECT 
ku.CONSTRAINT_NAME AS "Foreign key",
CONCAT("`", ku.TABLE_SCHEMA, "`.`", ku.TABLE_NAME, "`") AS "In",
GROUP_CONCAT(ku.COLUMN_NAME) AS "Source column",
CONCAT("`", ku.REFERENCED_TABLE_SCHEMA, "`.`", ku.REFERENCED_TABLE_NAME, "`") AS 
"References",
GROUP_CONCAT(ku.REFERENCED_COLUMN_NAME) AS "Target column"
FROM information_schema.KEY_COLUMN_USAGE AS ku
WHERE ku.REFERENCED_TABLE_SCHEMA = '[THE_CURRENT_SELECTED_SCHEMA_NAME]'
AND ku.REFERENCED_TABLE_NAME = '[THE_CURRENT_SELECTED_TABLE_NAME]'
GROUP BY ku.CONSTRAINT_NAME
HAVING `In` != `References`

Answer 6

Use Toad to load it up and you can view the references through the diagram. also make sure that you don't have any app code passing sql from the front-end, dropping the table may cause the app to break.

Download link http://www.toadsoft.com/toadmysql/FreewareDownload.htm

If you are using innoDB try this one SHOW TABLE STATUS FROM yourdatabasename LIKE 'T' http://dev.mysql.com/doc/refman/5.1/en/show-table-status.html

Answer 7

Look at the KEY_COLUMN_USAGE table in the iformation_schema schema.

Answer 8

from the mysql command line: show table status