sql - 多对多关系表的约束 - 两个相关记录都需要引用相同的依赖记录？

Question

共有三个实体，Student、Class 和Department。 Department 和 Student 是一对多的关系。 Department 和 Class 也是一对多的关系。 Student 和 Class 是多对多关系。

create table Department 
(
    Id int primary key,
    -- ...
)

create table Student 
(
    Id int primary key, 
    DepartmentId int not null references Department(Id), 
    -- ....
)

create table Class 
(
    Id int primary key, 
    DepartmentId int not null references Department(Id), 
    ....
)

下表是Student和Class之间的多对多关系。现在用户可以在表中放置一对来自不同(不应该被允许)部门的学生/类(class)。除了使用触发器之外，还有其他方法可以防止它吗？

create table StudentAndClass 
(
    StudentId int references Student(Id), 
    ClassId int references Class(Id), 
    -- ....
    primary key (StudentId, ClassId)
)

Answer 1

您可以在没有触发器或特殊功能的情况下执行此操作。这个想法是使用外键关系。 . .通过定义一组额外的键并将 DepartmentId(冗余地)添加到联结表:

create table Students (
    StudentId int primary key, 
    DepartmentId int not null references Department(Id), 
    -- ....
    unique (DepartmentId, StudentId)
);

create table Classes (
    ClassId int primary key, 
    DepartmentId int not null references Department(Id), 
    ....
    unique (DepartmentId, ClassId)
);

create table StudentClasses (
    DepartmentId int references Department(DepartmentId),
    StudentId int, 
    ClassId int, 
    -- ....
    primary key (StudentId, ClassId),
    foreign key (DepartmentId, StudentId) references (DepartmentId, StudentId),
    foreign key (DepartmentId, ClassId) references (DepartmentId, ClassId),
);

您可能不想要冗余，但没有触发器或特殊功能也是可能的。