## 如何在设计表格时实现一对一，一对多和多对多的关系？内容来源于 Stack Overflow，并遵循CC BY-SA 3.0许可协议进行翻译与使用

• 回答 (2)
• 关注 (0)
• 查看 (52)

### 2 个回答

```student: student_id, first_name, last_name, address_id
# "link back" if you need```

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

```Create table Gov(GID number(6) primary key,
Term_begin date,Term_end Date);

Create table State(SID number(3) primary key,
State_name varchar2(15), Population number(10),
SGID Number(4)  references GOV(GID),
CONSTRAINT GOV_SDID UNIQUE (SGID));

insert into gov(GID, Name, Address, term_begin)
values(110, 'Bob', '123 any st', '1-Jan-2009');

insert into state values(111, 'Virginia',
2000000, 110);```

1：M

```create table vendor1 ( vendor_no    number(4) primary key,
name varchar2(20),  address varchar2(20),  city        varchar2(15),  st varchar2(2),  zip varchar2(10),  contact  varchar2(16),  phone_no varchar2(12),  status  varchar2(8),
stamp_date     date);

create table inventory1 ( item   varchar2(6) primary key,
description  varchar2(30),  quantity_on_hand number(4) not null,
vendor_no   number(2) references vendor1(vendor_no),
reorder_qty  number(3) not null );```

```Create table Class (ClsID varchar2(10) primary Key,
title Varchar2(30),
Instructor Varchar2(30),
Day Varchar2(15),
time Varchar2(10));

Create table Student (StudID varchar2(15) primary Key,
Name Varchar2(35),
Major Varchar2(35),
Classyr Varchar2(10),
Status  Varchar2(10));

Create table Stud_class (studid Varchar2 (15) not null,
ClsId varchar2 (14) not null,
Foreign key (Studid) references Student(Studid),
Foreign key (ClsId) references Class(ClsID),
UNIQUE (Studid,ClsID));```