在设计表时，如何实现一对一、一对多和多对多的关系?

谁能举例说明在设计表时如何实现一对一、一对多和多对多关系?

499245 次浏览
小开
最佳答案

一对一:对引用的表使用外键:

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

你还必须在外键列(addess.student_id)上放置一个唯一的约束，以防止子表(address)中的多行与引用表(student)中的同一行相关。

一对多:在关系的多端使用外键链接回“one”端:

teachers: teacher_id, first_name, last_name # the "one" side
classes:  class_id, class_name, teacher_id  # the "many" side

多对多:使用连接表(例子):

student: student_id, first_name, last_name
classes: class_id, name, teacher_id
student_classes: class_id, student_id     # the junction table

示例查询:

 -- 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

小开

下面是一些现实生活中关系类型的例子:

一对一(1:1)

当且仅当表A中的一条记录与表B中的最多一条记录相关时，关系是一对一的。

为了建立一对一的关系，表B(没有孤记录)的主键必须是表a(有孤记录)的从键。

例如:

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);

一对多(1:M)

当且仅当表A中的一条记录为时，关系是一对多的 表B中的一条记录不能与表a中的多条记录相关。

为了建立一对多的关系，表a的主键(“一个”表)必须是表B的从键(“许多”表)。

例如:

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
);

多对多(M: M)

当且仅当表A中的一条记录与表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)
);
小开

一对一(1-1)关系: 这是初级和初级的关系;外键(只与一条记录的外键相关的主键)。这是一对一的关系

1-M (One to Many)关系: 这也是主&外键关系，但这里的主键与多个记录相关(即表A有图书信息，表B有一本书的多个出版商)

多对多(M-M):多对多包括两个维度，详细解释如下与样本。

-- 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
)
小开

一对多

一对多表关系如下所示:

一对多

在关系数据库系统中，一对多表关系基于子表中的Foreign Key列链接两个表，该列引用父表行的Primary Key。

在上面的图表中，post_comment表中的post_id列与post表id Primary Key列具有Foreign Key关系:

ALTER TABLE
post_comment
ADD CONSTRAINT
fk_post_comment_post_id
FOREIGN KEY (post_id) REFERENCES post

一对一的

一对一的表关系如下所示:

一对一

在关系数据库系统中，一对一的表关系基于子表中的Primary Key列链接两个表，该子表也是引用父表行的Primary Key的Foreign Key。

因此，我们可以说子表与父表共享Primary Key。

在上面的图表中，post_details表中的id列也与post表id Primary Key列具有Foreign Key关系:

ALTER TABLE
post_details
ADD CONSTRAINT
fk_post_details_id
FOREIGN KEY (id) REFERENCES post

多对多

多对多表关系如下所示:

多对多

在关系数据库系统中，多对多表关系通过包含两个Foreign Key列的子表链接两个父表，该子表引用两个父表的Primary Key列。

在上面的图表中，post_tag表中的post_id列与post表id Primary Key列也有Foreign Key关系:

ALTER TABLE
post_tag
ADD CONSTRAINT
fk_post_tag_post_id
FOREIGN KEY (post_id) REFERENCES post

并且，post_tag表中的tag_id列与tag表id Primary Key列具有Foreign Key关系:

ALTER TABLE
post_tag
ADD CONSTRAINT
fk_post_tag_tag_id
FOREIGN KEY (tag_id) REFERENCES tag

提交答案