inserting values into foreign key table and primary key table

Question

3.50/5 (2 votes)

See more:

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

Posted 12-Jan-14 6:58am

faizel s

Add a Solution

Comments

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 12-Jan-14 13:14pm

What have you tried?where is the problem?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

gobind_786 · Answer 1 · 2014-01-12T07:20:00

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:

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

Peter Leow · Answer 2 · 2014-01-12T15:28:00

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')

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')

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

Christian Graus · Answer 3 · 2014-01-12T12:53:00

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.

inserting values into foreign key table and primary key table

3 solutions

Solution 1

Solution 3

Solution 2

Add your solution here

Preview 0