如何获取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???
Thanks,
Lemiant
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(4)
获取给定表的外键的简单方法:
Simple way to get foreign keys for given table:
INFORMATION_SCHEMA 数据库包含所有其他数据库的完整架构的详细信息,包括约束:
http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
您还可以运行 SHOW CREATE TABLE 查询来获取创建表的 SQL,包括其约束。
The INFORMATION_SCHEMA database contains details of the full schema of all other databases, including constraints:
http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
You can also run a SHOW CREATE TABLE query to get the SQL to create a table, including its constraints.
正如 dev-null-dweller 所指出的,可以从 MySQL 的 information_schema 检索很多内容,包括外键。
1
使用函数
SCHEMA()
代替 dbname 在USE
中设置数据库的名称。2
正如 Dan Grossman 所指出的,该命令
基本上可用于获取创建表语句的 SQL 转储。
~3
MySQL提供了SHOW KEYS命令。因此,如果您知道较低的基数阈值并且表中几乎没有其他键,那么理论上您可以获得 FK。
由于每次内部数据库更改时密钥的基数都会更改,因此这是相当理论上的。例如,通过运行
ANALYZE TABLE
查看基数变化。~4
坚持命名模式很有用,例如
foreigntablename_foreignfieldname
。例如表billing
中的字段user_id
。大型 Web 内容框架的多个 ORM 使用此模式。Much can be retrieved from MySQL's
information_schema
, foreign keys included, as pointed out by dev-null-dweller.1
Instead of dbname use the function
SCHEMA()
to set the name of the database inUSE
.2
As pointed out by Dan Grossman, the command
can be used basically get an SQL dump of the create table statement.
~3
MySQL provides a SHOW KEYS command. As such you could theoretically get the FK if you know a lower cardinality threshold and have few other keys in the table.
As the key's cardinality changes each time the internal database is changed, this is rather theoretical. See the cardinality change for instance with running
ANALYZE TABLE
.~4
It is useful to stick to a naming schema, such as
foreigntablename_foreignfieldname
. For example the fielduser_id
in a tablebilling
. Several ORMs of big Web Content Frameworks use this schema.根据 Bill Karwin 在另一个线程中的回答,我使用这个解决方案获取我需要的所有信息,包括 on_delete 和 on_update 规则:
based on Bill Karwin answer in this other thread, I used this solution to get all the info I needed, included on_delete and on_update rules: