SQL 一对多和多对一关系的区别

Question

提问by Zhaf

What is the real difference between one-to-many and many-to-one relationship? It is only reversed, kind of?

一对多和多对一关系之间的真正区别是什么？它只是颠倒了，怎么样？

I can't find any 'good-and-easy-to-understand' tutorial about this topic other than this one: SQL for Beginners: Part 3 - Database Relationships

除了这个主题之外，我找不到任何关于此主题的“简单易懂”的教程：SQL 初学者：第 3 部分 - 数据库关系

Answer 1

采纳答案by Devendra D. Chavan

Yes, it a vice versa. It depends on which side of the relationship the entity is present on.

For example, if one department can employ for several employees then, department to employee is a one to many relationship (1 department employs many employees), while employee to department relationship is many to one (many employees work in one department).

是的，反之亦然。这取决于实体存在于关系的哪一边。

例如，如果一个部门可以雇用多个员工，那么部门与员工是一对多的关系（一个部门雇用多个员工），而员工与部门的关系是多对一的（多个员工在一个部门工作）。

More infoon the relationship types:

有关关系类型的更多信息：

Database Relationships - IBM DB2 documentation

数据库关系 - IBM DB2 文档

Answer 2

回答by nathan gonzalez

From this page about Database Terminology

从这个关于数据库术语的页面

Most relations between tables are one-to-many.
Example:
One area can be the habitat of many readers.
One reader can have many subscriptions.
One newspaper can have many subscriptions.
A Many to One relation is the same as one-to-many, but from a different viewpoint.
Many readers live in one area.
Many subscriptions can be of one and the same reader.
Many subscriptions are for one and the same newspaper.

大多数表之间的关系是一对多的。
例子：
一个区域可以成为许多读者的栖息地。
一个读者可以有多个订阅。
一份报纸可以有很多订阅。
多对一关系与一对多关系相同，但观点不同。
许多读者生活在一个地区。
许多订阅可以是同一个读者。
许多订阅是针对同一份报纸的。

Answer 3

回答by Gray

What is the real difference between one-to-many and many-to-one relationship?

一对多和多对一关系之间的真正区别是什么？

There are conceptual differences between these terms that should help you visualize the data and also possible differences in the generated schema that should be fully understood. Mostly the difference is one of perspective though.

这些术语之间存在概念上的差异，可以帮助您将数据可视化，并且生成的架构中可能存在的差异应该完全理解。不过，主要区别在于视角之一。

In a one-to-manyrelationship, the local table has one row that may be associated with many rows in another table. In the example from SQL for beginners, one Customermay be associated to many Orders.

在一对多关系中，本地表有一行可能与另一个表中的许多行相关联。在SQL 初学者的示例中，一个Customer可能与许多Orders相关联。

In the opposite many-to-onerelationship, the local table may have many rows that are associated with one row in another table. In our example, many Orders may be associated to one Customer. This conceptual difference is important for mental representation.

在相反的多对一关系中，本地表可能有许多行与另一个表中的一行相关联。在我们的示例中，许多Orders 可能与一个相关联Customer。这种概念上的差异对于心理表征很重要。

In addition, the schema which supports the relationship may be represented differently in the Customerand Ordertables. For example, if the customer has columns idand name:

此外，支持关系的模式可能在Customer和Order表中以不同的方式表示。例如，如果客户有列id和name：

id,name
1,Bill Smith
2,Jim Kenshaw

Then for a Orderto be associated with a Customer, many SQL implementations add to the Ordertable a column which stores the idof the associated Customer(in this schema customer_id:

然后，为了使 aOrder与 a 相关联Customer，许多 SQL 实现向Order表中添加了一个列，该列存储id了相关联的的Customer（在此模式中customer_id：

id,date,amount,customer_id
10,20160620,12.34,1
11,20160620,7.58,1
12,20160621,158.01,2

In the above data rows, if we look at the customer_idid column, we see that Bill Smith(customer-id #1) has 2 orders associated with him: one for $12.34 and one for $7.58. Jim Kenshaw(customer-id #2) has only 1 order for $158.01.

在上面的数据行中，如果我们查看customer_idid 列，我们会看到Bill Smith(customer-id #1) 有 2 个与他关联的订单：一个是 12.34 美元，一个是 7.58 美元。 Jim Kenshaw（客户 ID #2）只有 1 个订单，价格为 158.01 美元。

What is important to realize is that typically the one-to-many relationship doesn't actually add any columns to the table that is the "one". The Customerhas no extra columns which describe the relationship with Order. In fact the Customermight also have a one-to-many relationship with ShippingAddressand SalesCalltables and yet have no additional columns added to the Customertable.

需要注意的是，通常一对多关系实际上不会向“一”表中添加任何列。在Customer具有描述与关系没有额外列Order。事实上，它Customer也可能与ShippingAddress和SalesCall表具有一对多关系，但没有向Customer表中添加额外的列。

However, for a many-to-one relationship to be described, often an idcolumn is added to the "many" table which is a foreign-key to the "one" table -- in this case a customer_idcolumn is added to the Order. To associated order #10 for $12.34 to Bill Smith, we assign the customer_idcolumn to Bill Smith's id 1.

但是，对于要描述的多对一关系，通常会将一id列添加到“多”表中，该列是“一”表的外键——在这种情况下，将一customer_id列添加到Order. 要将 12.34 美元的订单 #10 关联到Bill Smith，我们将该customer_id列分配给Bill Smith的 id 1。

However, it is also possible for there to be another table that describes the Customerand Orderrelationship, so that no additional fields need to be added to the Ordertable. Instead of adding a customer_idfield to the Ordertable, there could be Customer_Ordertable that contains keys for both the Customerand Order.

但是，也可以有另一个表来描述Customer和Order关系，这样就不需要向Order表中添加额外的字段。不是添加的customer_id字段的Order表，可能有Customer_Order表，其中包含两个按键Customer和Order。

customer_id,order_id
1,10
1,11
2,12

In this case, the one-to-manyand many-to-oneis all conceptual since there are no schema changes between them. Which mechanism depends on your schema and SQL implementation.

在这种情况下，一对多和多对一都是概念性的，因为它们之间没有架构变化。哪种机制取决于您的架构和 SQL 实现。

Hope this helps.

希望这可以帮助。

Answer 4

回答by nvogel

There is no difference. It's just a matter of language and preference as to which way round you state the relationship.

没有区别。这只是一个语言和偏好的问题，关于你用哪种方式来陈述这种关系。

Answer 5

回答by pal

Answer to your first question is : both are similar,

你的第一个问题的答案是：两者都相似，

Answer to your second question is: one-to-many --> a MAN(MAN table) may have more than one wife(WOMEN table) many-to-one --> more than one women have married one MAN.

你的第二个问题的答案是：一对多 --> 一个 MAN(MAN 表) 可能有多个妻子(WOMEN 表) 多对一 --> 多个女人娶了一个 MAN。

Now if you want to relate this relation with two tables MAN and WOMEN, one MAN table row may have many relations with rows in the WOMEN table. hope it clear.

现在，如果您想将此关系与两个表 MAN 和 WOMEN 相关联，则一个 MAN 表行可能与 WOMEN 表中的行有许多关系。希望清楚。

Answer 6

回答by PerformanceDBA

Example

例子

Two tables with one relation

一张关系的两张表

SQL

In SQL, there is only one kind of relationship, it is called a Reference. (Your front end may do helpful or confusing things [such as in some of the Answers], but that is a different story.)

在 SQL 中，只有一种关系，称为引用。（您的前端可能会做有用或令人困惑的事情 [例如在某些答案中]，但那是另一回事。）

A Foreign Keyin one table (the referencingtable)
References
a Primary Keyin another table (the referencedtable)

In SQL terms, Bar references Foo
Not the other way around

CREATE TABLE Foo (
    Foo   CHAR(10)  NOT NULL, -- primary key
    Name  CHAR(30)  NOT NULL
    CONSTRAINT PK             -- constraint name
        PRIMARY KEY (Foo)     -- pk
    )  
CREATE TABLE Bar (
    Bar   CHAR(10)  NOT NULL, -- primary key
    Foo   CHAR(10)  NOT NULL, -- foreign key to Foo
    Name  CHAR(30)  NOT NULL
    CONSTRAINT PK                -- constraint name
        PRIMARY KEY (Bar),       -- pk
    CONSTRAINT Foo_HasMany_Bars  -- constraint name
        FOREIGN KEY   (Foo)      -- fk in (this) referencing table
        REFERENCES Foo(Foo)      -- pk in referenced table
    )

Since Foo.Foois a Primary Key, it is unique, there is only one row for any given value of Foo
Since Bar.Foois a Reference, a Foreign Key, and there is no unique index on it, there can be many rows for any given value of Foo
Therefore the relation Foo::Baris one-to-many
Now you can perceive(look at) the relation the other way around, Bar::Foois many-to-one
- But do not let that confuse you: for any one Barrow, there is just one Foorow that it References
In SQL, that is all we have. That is all that is necessary.

一个外键在一个表（和借鉴荷兰国际集团表）
引用
一个主键在另一表（和借鉴编表）

在 SQL 术语中，Bar 引用 Foo 而
不是相反

CREATE TABLE Foo (
    Foo   CHAR(10)  NOT NULL, -- primary key
    Name  CHAR(30)  NOT NULL
    CONSTRAINT PK             -- constraint name
        PRIMARY KEY (Foo)     -- pk
    )  
CREATE TABLE Bar (
    Bar   CHAR(10)  NOT NULL, -- primary key
    Foo   CHAR(10)  NOT NULL, -- foreign key to Foo
    Name  CHAR(30)  NOT NULL
    CONSTRAINT PK                -- constraint name
        PRIMARY KEY (Bar),       -- pk
    CONSTRAINT Foo_HasMany_Bars  -- constraint name
        FOREIGN KEY   (Foo)      -- fk in (this) referencing table
        REFERENCES Foo(Foo)      -- pk in referenced table
    )

由于Foo.Foo是主键，所以它是唯一的，任何给定的值只有一行Foo
由于Bar.Foo是一个引用，一个外键，并且没有唯一的索引，对于任何给定的值可以有很多行Foo
因此关系Foo::Bar是一对多的
现在你可以反过来感知（看）这种关系，Bar::Foo是多对一的
- 但是，不要让这种混淆你：对于任何一个Bar排，只有一个Foo排它引用
在 SQL 中，这就是我们所拥有的。这就是所有必要的。

What is the real difference between one to many and many to one relationship?

一对多和多对一关系之间的真正区别是什么？

There is only one relation, therefore there is no difference. Perception (from one "end" or the other "end") or reading it backwards, does not change the relation.

只有一种关系，因此没有区别。感知（从一个“端”或另一个“端”）或向后阅读，不会改变关系。

Cardinality

基数

Cardinality is declared first in the data model, which means Logical and Physical (the intent), and then in the implementation (the intent realised).

基数首先在数据模型中声明，这意味着逻辑和物理（意图），然后在实现中（意图实现）。

Cardinality

基数

One to zero-to-many
In SQL that (the above) is all that is required.

一对零对多
在 SQL 中，（以上）就是所需要的。

One to one-to-many
You need a Transactionto enforce the one in the Referencing table.

一对一
您需要一个事务来强制执行引用表中的一个。

One to zero-to-one
You need in Bar:

一对零对一
您需要Bar：

CONSTRAINT AK    -- constraint name
    UNIQUE (Foo) -- unique column, which makes it an Alternate Key

One to one
You need a Transactionto enforce the one in the Referencing table.

一对一
您需要一个事务来强制执行引用表中的事务。

Many to many
There is no such thing at the Physical level (recall, there is only one type of relation in SQL).

多对多
物理层没有这样的东西（回想一下，SQL 中只有一种类型的关系）。

At the early Logical levels during the modelling exercise, it is convenientto draw such a relation. Before the model gets close to implementation, it had better be elevated to using only things that can exist. Such a relation is resolved by implementing an Associative Table.

在建模练习期间的早期逻辑级别，绘制这样的关系很方便。在模型接近实现之前，最好将其提升为仅使用可以存在的东西。这种关系是通过实现关联表来解决的。

Many-to-many Resolved

多对多解决

Answer 7

回答by Premraj

One-to-Many and Many-to-One are similar in Multiplicity but not Aspect (i.e. Directionality).

一对多和多对一在多重性上相似，但在方面（即方向性）不相似。

The mapping of Associationsbetween entity classes and the Relationshipsbetween tables. There are two categories of Relationships:

实体类之间的关联映射和表之间的关系。有两类关系：

Multiplicity(ER term: cardinality)
- One-to-one relationships: Example Husband and Wife
- One-to-Many relationships: Example Mother and Children
- Many-to-Many relationships: Example Student and Subject
Directionality: Not affect on mapping but makes difference on how we can access data.
- Uni-directional relationships: A relationship field or property that refers to the other entity.
- Bi-directional relationships: Each entity has a relationship field or property that refers to the other entity.

多重性（ER 术语：基数）
- 一对一的关系：以丈夫和妻子为例
- 一对多关系：例如母亲和孩子
- 多对多关系：示例学生和主题
方向性：不会影响映射，但会影响我们访问数据的方式。
- 单向关系：引用其他实体的关系字段或属性。
- 双向关系：每个实体都有一个关系字段或属性来引用另一个实体。

Answer 8

回答by Samuel H

---One to Many--- A Parents can has two or more children.
---Many to one--- Those 3 children can have a single Parents.
Both are similar. This can be used belongs to the need. If you wanna find children for a particular parents, then u can go with One-To-Many. or else, wanna find parents for a twins, you may go with Many-To-One. Likewise....,

---一对多--- 父母可以有两个或更多的孩子。
---多对一--- 这三个孩子可以有一个单亲。
两者都很相似。这个可以使用属于需要。如果你想为特定的父母寻找孩子，那么你可以选择一对多。否则，想为双胞胎寻找父母，您可以选择多对一。同样地....，

Answer 9

回答by Tom Bell

There's no practical difference. Just use the relationship which makes the most sense given the way you see your problem as Devendra illustrated.

没有实际区别。考虑到您看待问题的方式，如 Devendra 所示，只需使用最有意义的关系即可。

Answer 10

回答by chandra

one-to-manyhas parent class contains n number of childrens so it is a collection mapping.

一对多的父类包含 n 个子类，因此它是一个集合映射。

many-to-onehas n number of childrens contains one parent so it is a object mapping

多对一有 n 个孩子，包含一个父母，所以它是一个对象映射

SQL 一对多和多对一关系的区别

提问by Zhaf

采纳答案by Devendra D. Chavan

回答by nathan gonzalez

回答by Gray

回答by nvogel

回答by pal

回答by PerformanceDBA

Example

例子

SQL

SQL

Cardinality

基数

回答by Premraj

回答by Samuel H

回答by Tom Bell

回答by chandra

相关推荐

最近更新

标签

SQL 一对多和多对一关系的区别

提问by Zhaf

采纳答案by Devendra D. Chavan

回答by nathan gonzalez

回答by Gray

回答by nvogel

回答by pal

回答by PerformanceDBA

Example

例子

SQL

SQL

Cardinality

基数

回答by Premraj

回答by Samuel H

回答by Tom Bell

回答by chandra

相关推荐

带条件的 SQL 求和

SQL 在列中查找重复条目

SQL Django 在/不在查询中

将选择结果转换为插入脚本 - SQL Server

相关推荐

最近更新

标签