SQL 设计表格时如何实现一对一、一对多和多对多的关系？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7296846/
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
How to implement one-to-one, one-to-many and many-to-many relationships while designing tables?
提问by arsenal
Can anyone explain how to implement one-to-one, one-to-many and many-to-many relationships while designing tables with some examples?
谁能解释一下如何在设计表格时实现一对一、一对多和多对多关系？
回答by NullUserException
One-to-one:Use a foreign key to the referenced table:
一对一：对引用的表使用外键：
student: student_id, first_name, last_name, address_id
address: address_id, address, city, zipcode, student_id # you can have a
# "link back" if you need
You must also put a unique constraint on the foreign key column (addess.student_id) to prevent multiple rows in the child table (address) from relating to the same row in the referenced table (student).
您还必须在外键列 ( addess.student_id)上设置唯一约束，以防止子表 ( address)中的多行与引用表 ( student) 中的同一行相关联。
One-to-many: Use a foreign key on the many side of the relationship linking back to the "one" side:
一对多：在链接回“一”方的关系的多方使用外键：
teachers: teacher_id, first_name, last_name # the "one" side
classes: class_id, class_name, teacher_id # the "many" side
Many-to-many: Use a junction table (example):
多对多：使用连接表（示例）：
student: student_id, first_name, last_name
classes: class_id, name, teacher_id
student_classes: class_id, student_id # the junction table
Example queries:
示例查询：
-- Getting all students for a class:
SELECT s.student_id, last_name
FROM student_classes sc
INNER JOIN students s ON s.student_id = sc.student_id
WHERE sc.class_id = X
-- Getting all classes for a student:
SELECT c.class_id, name
FROM student_classes sc
INNER JOIN classes c ON c.class_id = sc.class_id
WHERE sc.student_id = Y
回答by Arabinda Banik
Here are some real-world examples of the types of relationships:
以下是关系类型的一些真实示例：
One-to-one (1:1)
一对一 (1:1)
A relationship is one-to-one if and only if one record from table A is related to a maximum of one record in table B.
当且仅当表 A 中的一条记录与表 B 中最多一条记录相关时，关系是一对一的。
To establish a one-to-one relationship, the primary key of table B (with no orphan record) must be the secondary key of table A (with orphan records).
要建立一对一的关系，表B（没有孤立记录）的主键必须是表A（有孤立记录）的辅助键。
For example:
例如：
CREATE TABLE Gov(
GID number(6) PRIMARY KEY,
Name varchar2(25),
Address varchar2(30),
TermBegin date,
TermEnd date
);
CREATE TABLE State(
SID number(3) PRIMARY KEY,
StateName varchar2(15),
Population number(10),
SGID Number(4) REFERENCES Gov(GID),
CONSTRAINT GOV_SDID UNIQUE (SGID)
);
INSERT INTO gov(GID, Name, Address, TermBegin)
values(110, 'Bob', '123 Any St', '1-Jan-2009');
INSERT INTO STATE values(111, 'Virginia', 2000000, 110);
One-to-many (1:M)
一对多 (1:M)
A relationship is one-to-many if and only if one record from table A is related to one or more records in table B. However, one record in table B cannot be related to more than one record in table A.
当且仅当表 A 中的一条记录与表 B 中的一条或多条记录相关时，关系是一对多的。 然而，表 B 中的一条记录不能与表 A 中的多条记录相关。
To establish a one-to-many relationship, the primary key of table A (the "one" table) must be the secondary key of table B (the "many" table).
要建立一对多关系，表A（“一”表）的主键必须是表B（“多”表）的辅助键。
For example:
例如：
CREATE TABLE Vendor(
VendorNumber number(4) PRIMARY KEY,
Name varchar2(20),
Address varchar2(20),
City varchar2(15),
Street varchar2(2),
ZipCode varchar2(10),
Contact varchar2(16),
PhoneNumber varchar2(12),
Status varchar2(8),
StampDate date
);
CREATE TABLE Inventory(
Item varchar2(6) PRIMARY KEY,
Description varchar2(30),
CurrentQuantity number(4) NOT NULL,
VendorNumber number(2) REFERENCES Vendor(VendorNumber),
ReorderQuantity number(3) NOT NULL
);
Many-to-many (M:M)
多对多 (M:M)
A relationship is many-to-many if and only if one record from table A is related to one or more records in table B and vice-versa.
当且仅当表 A 中的一条记录与表 B 中的一条或多条记录相关时，关系是多对多的，反之亦然。
To establish a many-to-many relationship, create a third table called "ClassStudentRelation" which will have the primary keys of both table A and table B.
要建立多对多关系，请创建名为“ClassStudentRelation”的第三个表，该表将具有表 A 和表 B 的主键。
CREATE TABLE Class(
ClassID varchar2(10) PRIMARY KEY,
Title varchar2(30),
Instructor varchar2(30),
Day varchar2(15),
Time varchar2(10)
);
CREATE TABLE Student(
StudentID varchar2(15) PRIMARY KEY,
Name varchar2(35),
Major varchar2(35),
ClassYear varchar2(10),
Status varchar2(10)
);
CREATE TABLE ClassStudentRelation(
StudentID varchar2(15) NOT NULL,
ClassID varchar2(14) NOT NULL,
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (ClassID) REFERENCES Class(ClassID),
UNIQUE (StudentID, ClassID)
);
回答by Vlad Mihalcea
This is a very common question, so I decided to turn this answer into a an article.
这是一个很常见的问题，所以我决定把这个答案变成一篇文章。
One-to-many
一对多
The one-to-many table relationship looks as follows:
一对多表关系如下所示：
In a relational database system, a one-to-many table relationship links two tables based on a Foreign Keycolumn in the child which references the Primary Keyof the parent table row.
在关系数据库系统中，一对多表关系基于Foreign Key子Primary Key表中引用父表行的列的列链接两个表。
In the table diagram above, the post_idcolumn in the post_commenttable has a Foreign Keyrelationship with the posttable id Primary Keycolumn:
上表post_id中，post_comment表中的列Foreign Key与post表idPrimary Key列有关系：
ALTER TABLE
post_comment
ADD CONSTRAINT
fk_post_comment_post_id
FOREIGN KEY (post_id) REFERENCES post
One-to-one
一对一
The one-to-one table relationship looks as follows:
一对一的表关系如下所示：
In a relational database system, a one-to-one table relationship links two tables based on a Primary Keycolumn in the child which is also a Foreign Keyreferencing the Primary Keyof the parent table row.
在关系数据库系统中，一对一的表关系基于Primary Key子表中的列链接两个表，该列也是父表行的Foreign Key引用Primary Key。
Therefore, we can say that the child table shares the Primary Keywith the parent table.
因此，我们可以说子表Primary Key与父表共享。
In the table diagram above, the idcolumn in the post_detailstable has also a Foreign Keyrelationship with the posttable idPrimary Keycolumn:
在上面的表格图id中，post_details表格中的列也Foreign Key与post表格idPrimary Key列有关系：
ALTER TABLE
post_details
ADD CONSTRAINT
fk_post_details_id
FOREIGN KEY (id) REFERENCES post
Many-to-many
多对多
The many-to-many table relationship looks as follows:
多对多表关系如下所示：
In a relational database system, a many-to-many table relationship links two parent tables via a child table which contains two Foreign Keycolumns referencing the Primary Keycolumns of the two parent tables.
在关系数据库系统中，多对多表关系通过子表链接两个父表，子表包含Foreign Key引用Primary Key两个父表的列的两列。
In the table diagram above, the post_idcolumn in the post_tagtable has also a Foreign Keyrelationship with the posttable id Primary Keycolumn:
在上面的表图post_id中，post_tag表中的列也Foreign Key与post表idPrimary Key列有关系：
ALTER TABLE
post_tag
ADD CONSTRAINT
fk_post_tag_post_id
FOREIGN KEY (post_id) REFERENCES post
And, the tag_idcolumn in the post_tagtable has a Foreign Keyrelationship with the tagtable id Primary Keycolumn:
而且，tag_id在列post_tag表中有一个Foreign Key与关系tag表IDPrimary Key列：
ALTER TABLE
post_tag
ADD CONSTRAINT
fk_post_tag_tag_id
FOREIGN KEY (tag_id) REFERENCES tag
回答by Anjan Kant
One to one (1-1) relationship:This is relationship between primary & foreign key (primary key relating to foreign key only one record). this is one to one relationship.
一对一（1-1）关系：这是主外键之间的关系（主键与外键相关的只有一条记录）。这是一对一的关系。
One to Many (1-M) relationship:This is also relationship between primary & foreign keys relationships but here primary key relating to multiple records (i.e. Table A have book info and Table B have multiple publishers of one book).
一对多 (1-M) 关系：这也是主外键关系之间的关系，但这里的主键与多条记录有关（即表 A 有书籍信息，表 B 有多个出版商的一本书）。
Many to Many (M-M):Many to many includes two dimensions, explained fully as below with sample.
多对多（MM）：多对多包括两个维度，下面用示例充分解释。
-- This table will hold our phone calls.
CREATE TABLE dbo.PhoneCalls
(
ID INT IDENTITY(1, 1) NOT NULL,
CallTime DATETIME NOT NULL DEFAULT GETDATE(),
CallerPhoneNumber CHAR(10) NOT NULL
)
-- This table will hold our "tickets" (or cases).
CREATE TABLE dbo.Tickets
(
ID INT IDENTITY(1, 1) NOT NULL,
CreatedTime DATETIME NOT NULL DEFAULT GETDATE(),
Subject VARCHAR(250) NOT NULL,
Notes VARCHAR(8000) NOT NULL,
Completed BIT NOT NULL DEFAULT 0
)
-- This table will link a phone call with a ticket.
CREATE TABLE dbo.PhoneCalls_Tickets
(
PhoneCallID INT NOT NULL,
TicketID INT NOT NULL
)
