mysql - 如何根据外键值有条件地将数据插入表中？

Question

• 教师 table
• 学生表
• 老师有学生表

这是我的数据库中的三个表

| 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         |

在我的服务中，我收到 3 个值 - TeacherID、StudentID 和InstitutionID。我必须向“教师有学生”表中插入一条内容。但是，在将 TeacherID 和 StudentID 插入其中之前，我还必须确保它们都属于给定的 HospitalID。

目前，我尝试了两种不同的查询，都实现了任务。

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

但是，查询看起来很麻烦。这是正确的方法吗？或者有更好的方法来解决这个问题吗？我应该使用触发器吗？

Answer 1

您的第二个查询似乎是正确的继续方法，但是看起来您可以通过在 teacher_has_student 表中添加 institution_id 并使用此列定义外键来受益还有:

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