mysql - 如何根据外键值有条件地将数据插入表中?
问题描述
- 教师表
- 学生桌
- 老师有学生表
这是我数据库中的三个表
| 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 和机构 ID。我必须插入 Teacher Has Student 表。但是,在我向其中插入 TeacherID 和 StudentID 之前,我还必须确保它们都属于给定的机构 ID。
目前,我已经尝试了两种不同的查询来完成任务。
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
但是,查询似乎很麻烦。这是正确的方法吗?还是有更好的方法来解决这个问题?我应该使用触发器吗?
解决方案
您的第二个查询似乎是正确的继续方式,但是看起来您可以从institution_id
在teacher_has_student
表中添加中受益,并使用此列定义外键:
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);
推荐阅读
- html - 覆盖引导 CSS
- html - 添加新元素时HTML UL跳起来
- c++ - 为什么这个为右值赋值的 C++ 代码片段会编译?
- python - Django rest 框架 - 为类设置默认序列化程序
- c++ - C ++函数`getline()`对于char []不能正常工作
- excel - 范围和 .End(xlDown).Offset(1,0) 运行时错误 '1004'
- python - 使用熊猫和字典的 Python 查找和替换工具
- java - 连接到 Solace 时出现 CertificateException
- r - dplyr summarise 然后 summarise_at 在同一个管道中
- discord.py-rewrite - How can I mention whoever was mentioned in the command trigger?