如何获取mysql中的表的外键 [英] How can I get the foreign keys of a table in mysql

问题描述

我创建一个类，从数据库获取表，并将其显示到网页，尽可能多的功能。我想支持的事情之一是让类检测表中的哪些列对它们有外键约束，以便它可以去那些表，获取它们的所有值并在选择中使用它们-box，当你编辑这些字段，以避免有人违反外键约束时调用它

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???

感谢，

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`;

这篇关于如何获取mysql中的表的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！