mysql查询表外键_如何获取mysql中的表的外键

I am creating a class which, takes a table from a database, and displays it to a web-page, with as much functionality as possible. One of the things I would like to support, would be having the class detect which columns in the table have a foreign key constraint on them, so that it can then go to those tables, get all of their values and use them in a select-box which is called when you edit those fields, to avoid someone violating foreign key constraints,

The main problem is discovering which fields have a foreign key constraint on them, and which tables they are pointing to. Does anyone know how to do this???

Thanks,

Lemiant

解决方案

Simple way to get foreign keys for given table:

SELECT

`column_name`,

`referenced_table_schema` AS foreign_db,

`referenced_table_name` AS foreign_table,

`referenced_column_name` AS foreign_column

FROM

`information_schema`.`KEY_COLUMN_USAGE`

WHERE

`constraint_schema` = SCHEMA()

AND

`table_name` = 'your-table-name-here'

AND

`referenced_column_name` IS NOT NULL

ORDER BY

`column_name`;