一对多MySQL [英] One to Many MySQL

问题描述

可能重复：

MySQL关系

我正在尝试在MySQL中与外国人建立一对多的关系

I am trying to create a one to many relationship in MySQL with foreign keys.

两张表，用户和位置。每个用户可以有许多位置，但每个位置只能有一个用户。

Two tables, user and location. Each user can have many locations, but each location can have only one user.

如何配置？我正在使用HeidiSQL，如果这有帮助，虽然我可以输入代码。

How do I configure this? I am using HeidiSQL if that helps, though I can input code as well.

推荐答案

MySQL不知道，也不需要知道一个关系是否是1-1，或者是一对多。

没有SQL支持多个关系，所有这些都需要一个中间表，将多个关系分成2个单独的1个。

MySQL does not know, nor does it need to know if a relationship is 1-1, or 1-many.
No SQL supports many-many relationships, all require a intermediate table which splits a many-many relationship into 2 separate 1-many.

区别在于控制关系的逻辑，这是您编写的代码中的一个。

一个1-1关系由具有表共享相同的主键。

辅助表声明PK作为外键指向其他表PK。

The difference is in the logic that controls the relationships, which is in the code that you write.
A 1-1 relationship is maintained by having the tables share the same primary key.
With the secondary table declaring that PK as a foreign key pointing to the other tables PK.

Table chinese_mother (
id integer primary key,
name....


Table chinese_child (
id integer primary key,
name ....
....,
foreign key (id) references chinese_mother.id

关系的方向 1 - >很多 vs 许多< - 1 由链接字段的位置决定。

The direction of the relationship 1 -> many vs many <- 1 is determined by the location of the link field.

通常每个表都有唯一的 id ，链接字段称为 tablename_id 。

具有链接的表其中的字段是多个侧的关系，另一个表位于 1 侧。

Usually every table has a unique id and the link field is called tablename_id.
The table that has the link field in it is the many side of the relationship, the other table is on the 1 side.

每个用户可以有多个位置，但每个位置只能有一个用户。

Each user can have many locations, but each location can have only one user.

Table user
id: primary key
name......
.....

Table location
id: primary key
user_id foreign key references (user.id)
x
y
.......

通过将链接字段放在位置表中，您强制这样一个位置只能有1个用户。然而，用户可以具有许多位置。

By placing the link field in the location table, you force things so that a location can only have 1 user. However a user can have many locations.

这篇关于一对多MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！