一对多 MySQL [英] One to Many MySQL

问题描述

可能的重复:
MySQL 关系

我正在尝试使用外键在 MySQL 中创建一对多关系.

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

两个表，user 和 location.每个user可以有多个location，但每个location只能有一个user.

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 还是 1-many.
没有 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.

区别在于控制关系的逻辑，这在您编写的代码中.
通过让表共享相同的主键 (PK) 来维护 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 (PK).
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 ->many vs many <- 1 由链接字段的位置决定.

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

通常每个表都有一个唯一的id，链接字段称为tablename_id.
包含链接字段的表是关系的many 侧，另一个表在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
.......

通过将链接字段放在 location 表中，您可以强制要求一个位置只能有 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屋！