将值插入外键表和主键表 [英] inserting values into foreign key table and primary key table

问题描述

我有两个sql表，我必须将值插入这两个表中，以便主表中的一条记录与外键表中的几条记录相关联.请帮助我.上帝保佑您

Hi,
i have two sql tables and i have to insert values into these two tables such that a record in primary table is associated with several records in foreign key table.Pls help me.god bless you

推荐答案

假设您有两个表:

TAB_TEACHER
-id_teacher//主键，自动递增
-name_teacher//一个varchar

TAB_STUDENT
-id_student//主键，自动递增
-name_student//一个varchar
-id_teacher_fk//对教师的外键引用(TAB_TEACHER)

现在-使用一个预先存在的老师来插入一个新学生，所以我必须以老师的名字获取外键为:

Suppose you have two tables:

TAB_TEACHER
- id_teacher // primary key, autoincrement
- name_teacher // a varchar

TAB_STUDENT
- id_student // primary key, autoincrement
- name_student // a varchar
- id_teacher_fk // foreign key reference to a teacher (TAB_TEACHER)

Now - INSERT a new Student with an pre-existin TEACHER, so I have to get the foreign key with a teacher name as:

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

主键值必须先存在，然后才能用作另一个表中的外键.例如:
要将学生插入教师ID为"james123"的学生表中，必须确保此"james123"已存在于教师表中，否则必须首先插入该教师，例如

A primary key value must exist first before it can be used as foreign key in another table. For example:
To insert students into student table whose teacherid is "james123", you have to ensure that this "james123" already exists in the teacher table otherwise you have to insert this teacher first, e.g.

INSERT INTO teacher (teacherid, teachername) VALUES (''james123'',''James'')

插入老师后，您是否可以插入老师为"james123"的新学生，例如

After inserting the teacher then can you insert new students whose teacher is ''james123'', e.g.

INSERT INTO student (studentid, studentname, teacherid) VALUES (''student1'',''name of student1'',''james123'')

对于块插入，应遵循Christian Graus的解决方案.

For block insert, you should follow the solution by Christian Graus.

如果要将它们全部插入到单个插入语句中，则可以通过创建多组Values来轻松实现.在

插入tabstudent(''fred''，1'')，(''bill''，1)

其他

这与外键无关，如果您一次在一个语句中执行多次插入，或者一次执行一次，则从长远来看也没有任何区别.重要的是，在将主键用作外键之前，必须先存在这些主键.

If you want to insert them all in a single insert statement, you can do that easily, by creating many sets of Values as in

insert into tabstudent(''fred'', 1''), (''bill'', 1)

else

this has NOTHING to do with foreign keys, nor does it make ANY difference in the long run if you do many inserts in one statement, or one at a time. All that matters is that your primary keys need to exist before you use them as foreign keys.

这篇关于将值插入外键表和主键表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！