0

I have the following tables: school, teacher, school_teacher and student.

school_teacher is a many-to-many junction table using an autogenerated PK and the FKs of school and teacher.

A student belongs to a teacher of a particular school and a teacher of a school has many students.

My question is whether I should create the foreign key in the student table to point to school_teacher table PK or to the two tables PKs (school, teacher) individually?

I believe it would be best for referential integrity to reference school_teacher to ensure that transaction records are only inserted if there is a relationship between the school and the teacher. In terms of best practices, what is the best option or the best way to address this problem? Is there any other possibility better than the ones I mention?

In general, is there a problem in which the primary key of an association table is a foreign key in another table?

This is simply an example. In a real case a student could have more than one teacher. It is to simplify the example.

1

The culprit is the autogenerated key: school_teacher is a many-to-many junction table using an autogenerated PK and the FKs of school and teacher..

Why do you need it? If school and teacher is a candidate key I would concider that as the p.k.

EDIT: sketch

CREATE TABLE SCHOOLS
( SCHOOL_ID ... NOT NULL PRIMARY KEY
, ...
);

CREATE TABLE TEACHERS
( TEACHER_ID ... NOT NULL PRIMARY KEY
, ...
);

CREATE TABLE SCHOOL_TEACHERS
( SCHOOL_ID ... NOT NULL
      REFERENCES SCHOOLS (SCHOOL_ID)
, TEACHER_ID ... NOT NULL PRIMARY KEY
      REFERENCES TEACHERS (TEACHER_ID)

,     PRIMARY KEY (SCHOOL_ID, TEACHER_ID)
);

I find the rule "A student belongs to a teacher of a particular school" a bit odd, but if that is what you like to enforce:

CREATE TABLE STUDENTS
( STUDENT_NO NOT NULL PRIMARY KEY 
, SCHOOL_ID ... NOT NULL
, TEACHER_ID ... NOT NULL

,     FOREIGN KEY (SCHOOL_ID, TEACHER_ID)
      REFERENCES SCHOOL_TEACHERS (SCHOOL_ID, TEACHER_ID)
, ...
);
| improve this answer | |
  • If I want to have a student associated with a teacher and a school, what should be the foreign key in student table? The primary key of school_teacher or the corresponding primary keys of school and teacher. The idea of ​​creating a primmary key in the school_teacher table is to reference tuples in the student table when an association is made, if this is the best option. – Mr. Mars Feb 4 at 16:02
  • This is the main question: My question is whether I should create the foreign key in the student table to point to school_teacher table PK or to the two tables PKs (school, teacher) individually? I want to ensure that a student is assigned to a teacher who is assigned a school. – Mr. Mars Feb 4 at 16:21
  • @Mr.Mars, I have added a sketch in my answer – Lennart Feb 5 at 7:36
  • If you make two foreign keys to school and teacher, you risk ending up with a combination that does not exist in school_teachers – Lennart Feb 5 at 8:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.