Many to Many association table PK as FK in other table

foreign keymany-to-many

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.

Best Answer

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)
, ...
);

Related Solutions

Mysql – How to insert data into tables which reference each other?(MySQL)

Not sure I understood the task, but here is some food for thought for an alternative schema:

CREATE TABLE Departament(
    DepartamentID varchar(25) not null,
    Floor varchar(25) not null,
        CONSTRAINT pk_dept PRIMARY KEY (DepartamentID, Floor),
) ENGINE=InnoDB;


CREATE TABLE Employee(
    ID varchar(25) not null,
    DepartamentID varchar(25),
    Floor varchar(25),
        CONSTRAINT pk_ID PRIMARY KEY (ID),
        CONSTRAINT fk_dept FOREIGN KEY (DepartamentID, Floor) 
            REFERENCES Departament (DepartamentID, Floor)
) ENGINE=InnoDB;

CREATE TABLE Attendant (
    ID varchar(25) not null,
    DepartamentID varchar(25) not null,
    Floor varchar(25) not null,

    CONSTRAINT pk_attendent PRIMARY KEY (ID),
    CONSTRAINT ak_attendent UNIQUE (DepartamentID, Floor),
    CONSTRAINT fk_... REFERENCES Employee ...,
    CONSTRAINT fk_... REFERENCES Department ...,
) ENGINE=InnoDB;

Composite primary key from multiple tables / multiple foreign keys

Short version:

CREATE TABLE Relation
( stu_id INT NOT NULL REFERENCES Student,
  par_id INT NOT NULL REFERENCES Parent,
  PRIMARY KEY (stu_id, par_id)
) ;

Long version:

Why use short forms for names, like stu_id and par_id? Why not student_id? Saving typing 3-4 characters? How will you differentiate between parent_id and parameter_id? Or school_id and schoolmaster_id?
The name "Relation" is not very descriptive for a relationship. (Note also that in relational model terminology, "relation" has a meaning very close to "table".) I couldn't come with a good name though, so we could use "Guardian" or "Student_Parent" (this combination is often used in intersection tables)
The short version above is just an example. While it is working, it uses a lot of shortcuts, like the inline references. It's far better in my opinion, to name all constraints and declare all (primary, unique, foreign key and check) constraints after the column declarations, like in the long version below.
It's also good to choose some naming conventions anduse them consistently in all the tables, e.g. Tablename_PK for the primary keys, ReferencedTable_referencingTable_FK for the foreign keys, Something_UQ for the unique constraints, etc.

CREATE TABLE Guardian
(                                        -- columns
  student_id INT NOT NULL, 
  parent_id INT NOT NULL,
                                         -- constraints
  CONSTRAINT Guardian_PK                 -- the name of the PK constraint
    PRIMARY KEY (student_id, parent_id),

  CONSTRAINT Student_Guardian_FK         -- the name of the FK constraint
    FOREIGN KEY (student_id) 
    REFERENCES Student (student_id)
      ON UPDATE CASCADE                  -- the actions of the FK
      ON DELETE RESTRICT,
  CONSTRAINT Parent_Guardian_FK          -- second FK
    FOREIGN KEY (parent_id) 
    REFERENCES Parent (parent_id)
      ON UPDATE CASCADE
      ON DELETE RESTRICT
) ;

Best Answer

Related Solutions

Mysql – How to insert data into tables which reference each other?(MySQL)

Composite primary key from multiple tables / multiple foreign keys

Related Question