Two composite foreign keys have identical columns which must be equal value

Question

Let's say I have a relation: CourseRegistrations with composite primary key: (course_id, sem_id, student_id)

I have another relation: CourseEvaluations with composite primary key: (course_id, sem_id, section, type, serial) .The CourseEvaluations relation will contain records of the different evaluations done in a course.

Now I want to make a third relation: CourseStudentEvaluations with two composite foreign keys from both the above tables. Both the above tables' primary keys contain course_id and sem_id. How do I enforce that they are equal in a row/record so that I can ensure only those students' evaluations are entered against only those courses in which they're registered.

The CourseStudentEvaluations relation will contain records of all students' performance in each evaluation entered in CourseEvaluations.

Answer 1

If your database supports it, you can use a table level CHECK constraint to compare two columns to make sure that they're equal. You could have a single CHECK constraint that confirms the course_id and sem_id pointing to each parent are the same.

Alternatively, you could just have one course_id and one sem_id in your new table and use these columns in both of the foreign keys (one to each parent).

I think the second approach is a little cleaner, but if you're using some kind of ORM that has trouble understanding a child table column pointing at two different parents then you may not find the second approach to be practical.

Answer 2

I think your issue is a modeling one, as you skipped a few relations along the way. You can enforce your data consistency exclusively with the schema, no need for code enforcement with triggers or otherwise. Also, why use surrogate keys for such a model? Try using the natural keys which in your case are short, stable, and familiar, and see hot it simplifies your queries. Here is my take:

Courses:

Course PK <-- This is the name of the course, the one you use in the 'real world'

Semesters:

Semester PK <-- Summer, Spring, Winter, Fall or whatever you use to distinguish them

Course_Schedules:

Course REF Courses,

Semester REF Semesters,

Year ( in case not all courses are offered in the same semesters every year),

PK (Course, Semester, Year)

Students:

Student PK <-- Whatever you use to distinguish students in the real world

Course_Registrations:

Course,

Semester,

Year,

Student,

FK (Course, Semester, Year) REF Course_Schedules <-- Only 'real' scheduled courses,

PK (Course, Semester, Year, Student) <-- Student may repeat the course in different years

Course_Evaluations:

I think you are missing part of the key here, in the model you showed every course can have only one evaluation... Who evaluates it? add to PK as needed

Course,

Semester,

Year,

-- other properties --

PK (Course, Semester, Year, {missing attribute?}),

FK (Course, Semester, Year) REF Course Schedules

Course_Student_Evaluation:

Course,

Semester,

Year,

Student,

PK (Course, Semester, Year, Student),

FK (Course, Semester, Year) REF Course_Schedules

Now you can see that since both Course_Evaluations and Course_Student_Evaluations have DRI to Course_Schedules, it enforces everything you asked for, and more..

HTH