How to Insert Data into MySQL Tables with Foreign Key Constraints?

Inserting Data into Tables with Foreign Key Constraints in MySQL

Tables involving foreign key relationships require special considerations when inserting data to ensure data integrity. Let's explore how to handle insertions in MySQL for two scenarios:

Case 1: Inserting a Student with a Pre-existing Teacher

If you have a student record to insert and the teacher they belong to already exists, you can use a subquery to retrieve the foreign key (id_teacher) based on the 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>

Case 2: Inserting a Student with a New Teacher

If the student's teacher does not yet exist in the database, you need to perform two separate insert operations:

1. Insert the new teacher record into the TAB_TEACHER table:

<code class="sql">INSERT INTO TAB_TEACHER(name_teacher)
VALUES ('Dr. Smith')</code>

2. Use the id of the newly created teacher (id_teacher) as the foreign key when inserting the student:

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

The above is the detailed content of How to Insert Data into MySQL Tables with Foreign Key Constraints?. For more information, please follow other related articles on the PHP Chinese website!