如何使用mysql确定外键基数 [英] How to determine cardinality of foreign key using mysql

问题描述

我陷入一个必须使用mysql查找表之间关系的基数的问题。在这篇文章之后
MySQL：如何以编程方式确定外键关系？

我找到了与我的表相关的所有表以及作为外键的列。现在我也想找到关系的基数，即一对一，一对多或多对多。

I am stuck in a problem where i have to find cardinality of a relationship between tables using mysql. Following this post
MySQL: How to determine foreign key relationships programmatically?
I have found all tables related to my table and the columns which are foreign key. Now i also want to find the cardinality of relationship i.e. one-to-one, one-to-many or many-to-many. Any ideas or snippets would be highly appreciated

推荐答案

让我们假设表 A 有一个外键 f ，它引用表 B <的主键 k / code>。然后，您可以从架构中学习以下内容：

Let us assume that table A has a foreign key f which refers to the primary key k of table B. Then you can learn the following from the schema:

• 如果存在 UNIQUE 约束 Af ，那么中的每一行最多只能有一个 A 行B 。请注意，在多列索引的情况下，唯一约束的 all 列必须是外键的一部分。您可以使用表名的SHOOW INDEX WHERE Non_unique = 0 来获取有关表的唯一性约束的信息。


• 如果 Af 被声明为 NOT NULL ，那么在<$ c $中总是至少有 一行 A 中的每一行都是c> B 。您可以使用 SHOW COLUMNS FROM tablename 列出列，并查看其中哪些列允许 NULL 值。

• If there is a UNIQUE constraint on A.f, then there can be at most one row in A for every row in B. Note that in the case of multi-column indices, all columns of the unique constraint must be part of the foreign key. You can use SHOW INDEX FROM tablename WHERE Non_unique = 0 to obtain information on the uniqueness constraints of a table.
• If A.f is declared NOT NULL, then there will always be at least one row in B for every row in A. You can use SHOW COLUMNS FROM tablename to list the columns and see which of them allow NULL values.

如果将一个解释为零或一个，则使用一对一关系唯一约束和多对一关系（即 A 中的许多行引用 B中的一行）而没有这种唯一约束。

If you interpret "one" as "zero or one", then you get a one-to-one relation using a unique constraint, and a many-to-one relation (i.e. many rows in A referring to one row in B) without such a unique constraint.

多对多关系将使用单独的表进行建模，其中每一行代表关系的一个元素，其中包含的两个外键都具有多对一关系。

A many-to-many relation would be modeled using a separate table, where each row represents one element of the relation, with many-to-one relations for both foreign keys it contains.

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