How to Insert Values into MySQL Tables with Foreign Keys?

Inserting Values into Tables with Foreign Keys in MySQL

In MySQL, when creating tables with foreign key relationships, it's important to understand how to insert values correctly. Here's a breakdown of how to handle two scenarios:

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

To insert a student record with a foreign key referencing an existing teacher, retrieve the teacher's primary key based on their 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>

This query performs a subquery to select the teacher's 'id_teacher' value based on their name. The returned value is then used as the foreign key for the student record.

Case 2: Inserting a Student with a New Teacher

To insert a new student record along with a new teacher, you'll need to perform two separate insert statements:

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

INSERT INTO TAB_STUDENT(name_student, id_teacher_fk)
SELECT 'Alice The Student', LAST_INSERT_ID();</code>

In the first statement, a new teacher record is inserted. The 'LAST_INSERT_ID()' function is then used in the second statement to retrieve the primary key of the newly inserted teacher. This key is assigned as the foreign key for the student record.

By following these steps, you can ensure that foreign key relationships are maintained when inserting values into MySQL tables.

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