Mysql：如何在具有外键的表中插入值 [英] Mysql: How to insert values in a table which has a foreign key

问题描述

  TAB_TEACHER 
  -  id_teacher //主键，自动增量
  -  name_teacher // a varchar 
 
 TAB_STUDENT 
  -  id_student //主键，自动增量
  -  name_student // a varchar 
  -  id_teacher_fk //外键引用老师（TAB_TEACHER）
  

我想知道如何在这两种情况下插入： p>

案例1-插入一个预先存在教师的新学生，所以我必须得到带有教师姓名的外键

案例2 - 用新教师插入新学生（我在创建学生的同时创建的教师） 方案

http：//dev.mysql .com / doc / refman / 5.0 / en / insert-select.html

对于case1：

  INSERT INTO TAB_STUDENT（name_student，id_teach er_fk）
 SELECT'Joe The Student'，id_teacher 
 FROM TAB_TEACHER 
 WHERE name_teacher ='Jack'教授
限制1 
  

对于case2，您只需执行2个单独的插入语句

I have these two tables just for example:

TAB_TEACHER
- id_teacher   // primary key, autoincrement
- name_teacher // a varchar

TAB_STUDENT
- id_student    // primary key, autoincrement
- name_student  // a varchar
- id_teacher_fk // foreign key reference to a teacher (TAB_TEACHER)

I want to know how to insert in these two cases:

CASE 1- INSERT a new Student with an pre-existin TEACHER, so I have to get the foreign key with a teacher name

CASE 2- INSERT a new Student with a new TEACHER (the teacher I'm creating in the same time I'm creating the student)

解决方案

http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

For case1:

INSERT INTO TAB_STUDENT(name_student, id_teacher_fk)
SELECT 'Joe The Student', id_teacher
  FROM TAB_TEACHER
 WHERE name_teacher = 'Professor Jack'
 LIMIT 1

For case2 you just have to do 2 separate insert statements

这篇关于Mysql：如何在具有外键的表中插入值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！