如何在mysql中获取表的外键

2022-01-20 00:00:00 database parent-child foreign-keys php 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???

谢谢，

勒芒

推荐答案

获取给定表的外键的简单方法:

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

相关文章