CODEWITHSUNDEEP
mysqlsqldatabase
Praveen Kumar
Praveen Kumar

Reputation: 453

How to insert data into table conditionally based on foreign key value?

These are the three tables in my database

| TEACHER_ID | TEACHER_NAME | INSTITUTION_ID |
|------------|--------------|----------------|
| 1          | Stark        | 101            |
| 2          | Haydn        | 102            |

| STUDENT_ID | STUDENT_NAME | INSTITUTION_ID |
|------------|--------------|----------------|
| 11         | Parker       | 101            |
| 12         | Beethoven    | 102            |

| TEACHER_ID | STUDENT_ID |
|------------|------------|
| 1          | 11         |

In my service, I receive 3 values - TeacherID, StudentID and InstitutionID. I must make an insertion to the Teacher Has Student table. However, before I insert the TeacherID and StudentID to it, I must also ensure that they both belong to the InstitutionID given.

Presently, I have tried two different queries, which achieve the task.

INSERT INTO teacher_has_student 
            (teacher_id, 
             student_id) 
VALUES      ((SELECT teacher_id 
              FROM   teacher 
              WHERE  teacher_id = 2 
                     AND institution_id = 102), 
             (SELECT student_id 
              FROM   student 
              WHERE  student_id = 12 
                     AND institution_id = 102))

INSERT INTO teacher_has_student (teacher_id, student_id)
SELECT teacher_id, student_id 
FROM   teacher 
       JOIN   student
where  teacher_id = 2 
       AND    student_id = 12 
       AND    teacher.institution_id = 102 
       AND    student.institution_id = 102

However, the queries seem cumbersome. is this the right way to do it? Or is there a better way to approach this? Should I use triggers?

Upvotes: 0

Views: 486

Answers (1)

Hrabal
Hrabal

Reputation: 2523

Your second query seems the correct way to proceed, however looks like you can benefit from adding the institution_id in the teacher_has_student table, and define the foreign key with this column as well:

CREATE TABLE teacher (
    teacher_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    teacher_name VARCHAR(50) NOT NULL,
    institution_id INT(10) UNSIGNED,
    PRIMARY KEY (teacher_id),
    UNIQUE KEY teacher_institution (teacher_id, institution_id)
);
CREATE TABLE student (
    student_id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    student_name VARCHAR(50) NOT NULL,
    institution_id INT(10) UNSIGNED,
    PRIMARY KEY (student_id),
    UNIQUE KEY student_institution (student_id, institution_id)
);
CREATE TABLE teacher_has_student (
    teacher_id INT(10) UNSIGNED NOT NULL,
    student_id INT(10) UNSIGNED NOT NULL,
    institution_id INT(10) UNSIGNED NOT NULL,
    UNIQUE KEY (teacher_id, student_id, institution_id),
    CONSTRAINT teacher_istitution FOREIGN KEY (teacher_id, institution_id) REFERENCES teacher (teacher_id, institution_id),
    CONSTRAINT student_istitution FOREIGN KEY (student_id, institution_id) REFERENCES student (student_id, institution_id)
);
INSERT INTO teacher (teacher_name, institution_id)
VALUES ("Stark", 101), ("Haydn", 102);
INSERT INTO student (student_name, institution_id)
VALUES ("Parker", 101), ("Beethoven", 102);

/* THIS ONE WORKS for both student 2 and teacher 2 have institution_id 102 */
INSERT INTO teacher_has_student (teacher_id, student_id, institution_id)
VALUES (2, 2, 102);

/* foreign key constraint fails: for none of theacher and student have institution_id 101 */
INSERT INTO teacher_has_student (teacher_id, student_id, institution_id)
VALUES (2, 2, 101);

/* foreign key constraint fails: for none of theacher have no institution_id 101 */
INSERT INTO teacher_has_student (teacher_id, student_id, institution_id)
VALUES (2, 1, 101);

/* foreign key constraint fails: for none of student have no institution_id 101 */
INSERT INTO teacher_has_student (teacher_id, student_id, institution_id)
VALUES (1, 2, 101);

Upvotes: 1

Related Questions