设计表时如何实现一对一，一对多和多对多关系？

Question

任何人都可以解释在设计带有示例的表时如何实现一对一，一对多和多对多关系吗？

Answer 1

一对一： 使用外键访问被引用的表：

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）中的同一行相关。

一对多 ：在关系的许多方面使用外键链接回“一个”侧：

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