I have the following tables:
school_teacher is a many-to-many junction table using an autogenerated PK and the FKs of
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 (
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.