database 数据库中一对一和一对多关系的区别

Difference between one-to-one and one-to-many relationship in database

提问by LuckyLuke

This is probably a basic(dumb) question but when having a one-to-one relationship in a database the other table has a foreign key ID(in this example). And in a one-to-many relationship the table contains many foreign keys.

这可能是一个基本（愚蠢）的问题，但是当在数据库中具有一对一关系时，另一个表具有外键 ID（在本例中）。在一对多关系中，该表包含许多外键。

But the database does not know whether this is a one-to-one or one-to-many relationship right? The relationships that I make in an ER-Diagram is only to indicate where it should be foreign keys when making the actual tables?

但是数据库不知道这是一对一还是一对多的关系吧？我在 ER-Diagram 中建立的关系只是为了表明在制作实际表时它应该是外键的位置？

I do not completely grasp the idea of the relationships, even though I have read many tutorials about this.

尽管我已经阅读了很多关于此的教程，但我并没有完全掌握这些关系的概念。

Thanks in advance.

提前致谢。

回答by Zack Bloom

In a sense, all the relationships we talk about are not knownto the database, they are constructs we have invented to better understand how to design the tables.

从某种意义上说，我们谈论的所有关系都不为数据库所知，它们是我们为了更好地理解如何设计表而发明的结构。

The big difference in terms of table structure between one-to-one and one-to-many is that in one-to-one it is possible (but not necessary) to have a bidirectional relationship, meaning table A can have a foreign key into table B, and table B can have a foreign key into the associated record in table A. This is not possible with a one-to-many relationship.

一对一和一对多在表结构方面的最大区别在于，在一对一中可能（但不是必须）有双向关系，这意味着表 A 可以有外键进入表 B，并且表 B 可以有一个外键进入表 A 中的关联记录。这在一对多关系中是不可能的。

One-to-one relationships associate one record in one table with a single record in the other table. One-to-many relationships associate one record in one table with many records in the other table.

一对一关系将一张表中的一条记录与另一张表中的一条记录相关联。一对多关系将一张表中的一条记录与另一张表中的多条记录相关联。

回答by Danil

To enable one-to-one relationship you need to add unique constraint to foreign key. It is not possible to have two foreign keys for each table as it will be impossible to create records.

要启用一对一关系，您需要向外键添加唯一约束。每个表不可能有两个外键，因为创建记录是不可能的。

回答by hvgotcodes

Im having trouble understanding what the actual question is.

我无法理解实际问题是什么。

Your analysis is for the most part correct, in that if you have a 2 tables, and table2 has a foreign key to table one, it could be either a one-to-one or a many-to-one.

您的分析在很大程度上是正确的，因为如果您有 2 个表，并且 table2 有一个指向第一个表的外键，则它可以是一对一或多对一。

Your sentence "And in a one-to-many relationship the table contains many foreign keys."

您的句子“并且在一对多关系中，该表包含许多外键。”

The table of the 'many' side still contains one column that is a foreign key, its just that more than one row can have the same foreign key value (many rows point to one parent).

“多”方的表仍然包含一列作为外键，只是多行可以具有相同的外键值（多行指向一个父）。

Also note that you can put the foreign key on the parent table, to the child, instead of the other way around. In this way, you can prevent one-to-many if you want to do that. Also note that in this way, more than one parent can share a child, which might or might not be what you want.

另请注意，您可以将外键放在父表上，给子表，而不是相反。通过这种方式，如果你想这样做，你可以防止一对多。另请注意，通过这种方式，多个家长可以共享一个孩子，这可能是您想要的，也可能不是。

回答by Adam Robinson

The database-level equivalent of a 1:1 vs. 1:m is having a unique index on the foreign key column. Note that this will only work for 1:1, NOT 1:0..1, as nullis considered when evaluating uniqueness. There are workarounds for this restriction, but that's it at the basic level.

1:1 与 1:m 的数据库级等效是在外键列上有一个唯一索引。请注意，这仅适用于 1:1，而不适用于 1:0..1，正如null在评估唯一性时所考虑的那样。此限制有一些解决方法，但这就是基本级别。

回答by Sohail xIN3N

Similarly by example, a product has only one product code, so it's one-to-one relationship (product <-> ABC123), but a customer can purchase more than one product, so it's one-to-many relationship (person <->>>product).

同样举个例子，一个产品只有一个产品代码，所以是一对一关系（product <-> ABC123），但是一个客户可以购买多个产品，所以是一对多关系（person <- >>>产品）。

回答by Maxym

well, you are right, this relation is important for you, but not for db itself. When you have two tables, one with your basic information, and another one with your detailed information.. for both tables you are you, so it is one-to-one relation, you can not map your data to somebody else.

好吧，你是对的，这种关系对你很重要，但对 db 本身并不重要。当你有两张表，一张是你的基本信息，另一张是你的详细信息。对于这两张表，你就是你，所以是一对一的关系，你不能将你的数据映射到其他人。

Now add third table "cities" and one of your information points to city you live in - this is example of one-to-many (one city can be used, and should be used for many people).

现在添加第三个表“城市”和您居住的城市的信息点之一 - 这是一对多的示例（可以使用一个城市，并且应该用于许多人）。

one-to-many / one-to-one just show how your tables interact. And all the time, you want to "save" rows/columns in table not duplicating them you will use one-to-many relation with another table. Or many-to-many :)

一对多/一对一只是显示您的表如何交互。并且一直以来，您都希望“保存”表中的行/列而不是复制它们，您将使用与另一个表的一对多关系。或多对多:)

回答by nvogel

Let's assume you have a table with two attributes A and B. If A is a candidate key and B is not then the relationship between A and B is 1 to many. If both A and B are candidate keys then the relationship is 1 to 1.

假设您有一个包含两个属性 A 和 B 的表。如果 A 是候选键而 B 不是，那么 A 和 B 之间的关系是一对多。如果 A 和 B 都是候选键，则关系为 1 比 1。

回答by Paul Keister

Given table A and B if

给定表 A 和 B 如果

1. A and B have a strict 1 to 1 relationship
2. For every B instance, there will always be an A instance

1. A 和 B 有严格的 1 对 1 关系
2. 对于每个 B 实例，总会有一个 A 实例

The best approach is to make the primary key of B also a foreign key referencing A. This is also called "Table per Type Inheritance" and the "is a" relationship. There are other ways to enforce a unique foreign key, but using the primary key makes the relationship clear in the schema and in ER diagrams.

最好的方法是使 B 的主键也是引用 A 的外键。这也称为“每个类型继承的表”和“是一个”关系。还有其他方法可以强制使用唯一的外键，但使用主键可以使架构和 ER 图中的关系变得清晰。

Of course there are always other scenarios, and if your design doesn't meet both of the criteria above, you'll have to use another approach.

当然，总会有其他场景，如果您的设计不满足上述两个标准，您将不得不使用另一种方法。