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:
<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>
Scenario 2: Simultaneously Creating a New Teacher and Student
When creating both a new student and a non-existent teacher:
<code class="sql">-- 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);</code>
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.
The above is the detailed content of How to Handle Foreign Key Insertion in MySQL Queries: Two Common Scenarios. For more information, please follow other related articles on the PHP Chinese website!