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

内容来源于 Stack Overflow,并遵循CC BY-SA 3.0许可协议进行翻译与使用

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

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

提问于
用户回答回答于

一对一:

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

一对多

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, 
Name varchar2(25), Address VarCHAR2(30), 
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));

扫码关注云+社区