SQL db design constraints for co-belonging

Question

Here is my database structure, it contains 3 tables : School, Student, Course

Each student must belong to a school (so School ID will be a foreign key in the student table referring to the School table).

Each course also must belong to a school (so School ID will be a foreign key in the course table referring to the School table).

Finally, each student may sign up for one or more courses (pivot table with a foreign key for student and a foreign key for school), but with the restriction that the student and the course must both belong to the same school. A student cannot be allowed to sign up for a course which belongs to a different school from the student.

Is there a way to add this constraint in the pivot table, or some other way model it?

Or should the constraint be enforced in the code before each insert on the pivot table?

Answer 1

You can add the SchoolID as a part Primary keys in both Student and Course.

This will force foreign keys to those tables to also specify the SchoolID.

We use this fact in our StudentInCourse-table to force both the student and the course to belong to the same school.

CREATE TABLE School(id INT PRIMARY KEY);

CREATE TABLE Course(id int, schoolID INT 
    FOREIGN KEY REFERENCES dbo.School(id), 
    PRIMARY KEY(id, schoolID));

CREATE TABLE Student(id INT, schoolID INT 
    FOREIGN KEY REFERENCES dbo.School(id), 
    PRIMARY KEY(id, SchoolID));

CREATE TABLE StudentInCourse(StudentId INT, schoolId INT, CourseID INT, 
    CONSTRAINT [fk_student] 
    FOREIGN KEY (StudentId,schoolID) REFERENCES student(id, SchoolID),
    CONSTRAINT [fk_course] 
    FOREIGN KEY (CourseID,schoolID) REFERENCES Course(id, SchoolID),
    );


INSERT dbo.School ( id )
VALUES  ( 1 ), ( 2 );

INSERT dbo.Student
        ( id, schoolID )
VALUES  ( 19950516, 1  );

INSERT dbo.Course
        ( id, schoolID )
VALUES  ( 77666, 1     ),
        ( 99988, 2     );

-- Student in same school as course is OK
INSERT dbo.StudentInCource
        ( StudentId, schoolId, CourseID )
VALUES  ( 19950516,  1,        77666    );


-- Student in other school as course is FAIL
INSERT dbo.StudentInCourse
        ( StudentId, schoolId, CourseID )
VALUES  ( 19950516,  2,        99988    );
INSERT dbo.StudentInCourse
        ( StudentId, schoolId, CourseID )
VALUES  ( 19950516,  1,        99988    );