How to insert values in table with foreign key using MySQL?

Question

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

Answer 1

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

Answer 2

Case 1: Insert Row and Query Foreign Key

Here is an alternate syntax I use:

INSERT INTO tab_student     SET name_student = 'Bobby Tables',        id_teacher_fk = (        SELECT id_teacher          FROM tab_teacher         WHERE name_teacher = 'Dr. Smith') 

I'm doing this in Excel to import a pivot table to a dimension table and a fact table in SQL so you can import to both department and expenses tables from the following:

Case 2: Insert Row and Then Insert Dependant Row

Luckily, MySQL supports LAST_INSERT_ID() exactly for this purpose.

INSERT INTO tab_teacher    SET name_teacher = 'Dr. Smith'; INSERT INTO tab_student     SET name_student = 'Bobby Tables',        id_teacher_fk = LAST_INSERT_ID()