一对多 MySQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8217742/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
One to Many MySQL
提问by Baub
Possible Duplicate:
MySQL Relationships
可能的重复：
MySQL 关系
I am trying to create a one to many relationship in MySQL with foreign keys.
我正在尝试使用外键在 MySQL 中创建一对多关系。
Two tables, userand location. Each usercan have many locations, but each locationcan have only one user.
两张桌子，user和location。每个都user可以有多个locations，但每个location只能有一个user。
How do I configure this? I am using HeidiSQL if that helps, though I can input code as well.
我该如何配置？如果有帮助，我正在使用 HeidiSQL，尽管我也可以输入代码。
回答by Johan
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.
MySQL 不知道，也不需要知道关系是 1-1 还是 1-many。
没有 SQL 支持多对多关系，都需要一个中间表，将多对多关系拆分为 2 个单独的 1 对多关系。
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.
不同之处在于控制关系的逻辑，这在您编写的代码中。
通过让表共享相同的主键来维护 1-1 关系。
辅助表将 PK 声明为指向其他表 PK 的外键。
Table chinese_mother (
id integer primary key,
name....
Table chinese_child (
id integer primary key,
name ....
....,
foreign key (id) references chinese_mother.id
The direction of the relationship 1 -> manyvs many <- 1is determined by the location of the link field.
关系1 -> manyvs的方向many <- 1由链接字段的位置决定。
Usually every table has a unique idand the link field is called tablename_id.
The table that has the link field in it is the manyside of the relationship, the other table is on the 1side.
通常每个表都有一个唯一id的链接字段被称为tablename_id。
其中有链接字段many的表是关系的一侧，另一个表在1一侧。
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
.......
By placing the link field in the locationtable, you force things so that a location can only have 1 user. However a user can have many locations.
通过在location表格中放置链接字段，您可以强制要求一个位置只能有 1 个用户。然而，用户可以有多个位置。
回答by Andreas Wederbrand
There is an example here that is almost exactly what you need foreign keys in innodb
这里有一个例子，几乎正是你在 innodb 中需要的外键
CREATE TABLE parent (
id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;
CREATE TABLE child (
id INT,
parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE
) ENGINE=INNODB;
In your example user is the same as parent (a user has many locations, a parent has many childs) and location is the same as child (a location has one user, a child has one parent)
在您的示例中，用户与父级相同（一个用户有多个位置，父级有多个子级）并且位置与子级相同（一个位置有一个用户，一个子级有一个父级）
