Skip to content
Advertisement

Foreign key as primary key?

I currently have 2 tables STUDENTS and SUBJECTS. I need to create another table for ENROLLMENTS, the thing is that in that table I need to know the minimal attendance, which is both dependent on student (if he has a job it will be lower) and on subject (each subject has a base minimal attendance). Should I create another table like MINIMAL_ATTENDACE which holds the base minimal attendance of each Subject ? If so, is it good to have the primary key in that table be also the primary key from SUBJECTS ?

For example I have :

Advertisement

Answer

You can use a several constraints on a single column. Apparently, there is a many-to-many relationship between STUDENT and SUBJECT. A possible approach for implementing this (with SQL) is an “intersection” or “bridge” table is. You can use a composite PK, and foreign key constraints like this:

enter image description here

Demo (dbfiddle)

In the example (above), we assume that {1} the “hired” attribute belongs to a STUDENT, and {2} the different “attendance” attributes are attributes of SUBJECT.

You can now just join the 3 tables, and find the required attendance for each student using the query below.

If the “hire” status of a STUDENT can change during an academic year (or semester), you’d probably need to add some more entities (and subsequently: tables) for keeping track of each STUDENT’s hire state change.

User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement