Find the table(column) that a FK links to? MySql

Question

I am ok with mysql. when i use desc tablename i can see columns with MUL meaning it is a foreign key. Now my question is, how do i find out exactly what table and column that column is pointing to.

Answer 1

You can use the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table.

select * 
from information_schema.key_column_usage 
where table_schema = YOUR_DB and table_name = YOUR_TABLE 
order by constraint_name, poisition_in_unique_constraint

The table_schema, table_name, and column_name are the ones which reference the foreign key table. The referenced_table_schema, referenced_table_name and referenced_column_name will contain the information about the referenced table.

Answer 2

Issue

SHOW CREATE TABLE tablename