How to Tackle Foreign Key Insertion in MySQL Queries

To efficiently insert values into tables with foreign keys, let's explore two common scenarios:

Scenario 1: Adding a Student with an Existing Teacher

To link a new student to a pre-existing teacher, retrieve the foreign key using a teacher's name:

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;

Scenario 2: Simultaneously Creating a New Teacher and Student

When creating both a new student and a non-existent teacher:

-- Insert a new teacher first
INSERT INTO TAB_TEACHER(name_teacher)
VALUES ('Professor Jade');

-- Retrieve the newly created teacher's ID
SET @teacher_id = LAST_INSERT_ID();

-- Insert the new student with the foreign key pointing to the new teacher
INSERT INTO TAB_STUDENT(name_student, id_teacher_fk)
VALUES ('Mia The Student', @teacher_id);

In this scenario, the LAST_INSERT_ID() function is used to capture the ID of the newly inserted teacher for immediate use as a foreign key for the student.