How to Insert Values with Foreign Keys in MySQL Tables: Existing vs. New Keys?

Inserting Values with Foreign Keys in MySQL Tables

In database management, it's common to create tables with foreign key constraints to establish relationships between them. This question explores how to insert values into tables with foreign keys, considering two scenarios.

Scenario 1: Inserting with an Existing Foreign Key Value

To insert a student record with an existing teacher record and retrieve the foreign key via the teacher's name, use the following query:

<code class="sql">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</code>

This query ensures that the inserted student record links to the correct teacher based on the provided name.

Scenario 2: Inserting with a New Foreign Key Value

For inserting both a new student and a new teacher simultaneously, the following procedure should be followed:

1. Insert the teacher record:

   <code class="sql">INSERT INTO TAB_TEACHER(name_teacher)
   VALUES ('Professor Jane')</code>

2. Retrieve the newly inserted teacher's ID:

   <code class="sql">SET @new_teacher_id = LAST_INSERT_ID()</code>

3. Insert the student record with the foreign key set to the new teacher's ID:

   <code class="sql">INSERT INTO TAB_STUDENT(name_student, id_teacher_fk)
   VALUES ('Mary The Student', @new_teacher_id)</code>

By executing these steps, a new student record linked to the newly created teacher record will be inserted into the database.

The above is the detailed content of How to Insert Values with Foreign Keys in MySQL Tables: Existing vs. New Keys?. For more information, please follow other related articles on the PHP Chinese website!