Is it possible to determine the table linked to by a foreign key?

Question

I have a table containing a foreign key and I know the column name of that foreign key.

Is it possible, using this information, to determine the table to which that key links?

The reason for this is that the foreign key is dynamic and so I cannot determine the linked table in advance.

Answer 1

Provided you are using MySQL 5.1 or later, you can use REFERENTIAL_CONSTRAINTS table from information schema. Something like:

select references_table_name
  from referential_constraints
 where table_name = 'your_table';

Answer 2

select referenced_table_name, referenced_column_name
 from information_schema.key_column_usage
where table_name = 'Yourtable' and column_name='id'

Answer 3

SELECT CONSTRAINT_NAME, ORDINAL_POSITION, COLUMN_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE
TABLE_NAME = 'table_name' AND
TABLE_SCHEMA = 'database_name' AND
REFERENCED_TABLE_SCHEMA = 'database_name';

ORDINAL_POSITION may be of interest when you have composite keys.