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

2021-12-01 00:00:00 sql oracle database-design

谁能用一些例子解释一下如何在设计表格时实现一对一、一对多和多对多的关系?

Can anyone explain how to implement one-to-one, one-to-many and many-to-many relationships while designing tables with some examples?

推荐答案

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

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

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

一对多:在链接回一"方的关系的多方使用外键:

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

多对多:使用联结表(示例):

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

相关文章