How do I insert information in a SQL table if the table has a foreign key in it?

Question

Say I have a user table with the fields: Name, Email, Telephone, IDCareer

In this table IDCareer is the foreign key.

I have another table called Career with the fields: IDCareer, CareerName

My plan it to have a ComboBox on my form and pulls a list of available careers directly from my DB. My problem is this is my first time using SQL to insert things in two tables at once (is this even what I should be doing?)

For instance, to insert into the first table, I'd go:

*Insert into User values ('Sergio','[email protected]','345', and here I'm lost

What should I put into the end so it saves properly?

Edit: Thanks a lot for all your answers. As always SO is a great learning tool. :D

Answer 1

If you're picking from a predefined list of careers in your career table, the select for the career should have a visible string of the CareerName and a value of IDCareer. When the user selects one, you have the id value that goes into your insert SQL. Suppose your table is populated with an id of 3 for career Fire fighter. When the user picks Fire fighter, your SQL would put 3 in the 'and here I'm lost' from your post.

If the issue is that you're collecting new values, you must put them in first, get the id number of the newly inserted career (you're using an auto_increment (MySQL), sequence (Oracle), or identity (SQL Server) for those IDCareer values, yes?) using whatever function returns the last inserted id value, and put that in your 'and here I'm lost'.

Answer 2

Usually you would say the comboBox is bound with the values of the Career Table.

you can configure the ComboBox.

Current value using the ValueMember property, Text of each member with DisplayMember property.

Then, you can call cbo.SelectedValue for getting the Career selected value.

// Configure comboBox
DataTable dtCareer = GetCareerTable();
cboCareer.DataSource = dtCareer;
cboCareer.DisplayMember = "CareerName";
cboCareer.ValueMember = "IDCareer";



// Get selected career
if (cboCareer.SelectedIndex != -1) {
    int career = (int) cboCareer.SelectedValue;
    // Do your insert here
}

Answer 3

You should insert the primary key of the Career table, ie. the value for IDCareer.

The natural thing to do is to populate the Combo box with values from the Career table - display the values from the CareerName column, but keep the values from the IDCareer column. When the user chooses a career, grab the IDCareer value (probably an integer), then save that to the User table.

Adding new careers to the Career table is usually a different activity, to be done in another form.

Answer 4

If you have a drop down box, then you probably don't need to insert anything into the second table as you will already have the ID value from that. All you need to do is insert into your main table with:

INSERT INTO User (Name, Email, Telephone, IDCareer) VALUES ('person', '[email protected]', '1234', 45);

If however, you'd like to create a new record in the foriegn table (i.e. you're letting them create a new type of career) - then you can set it up as a transaction and insert into your 'foreign' table first, get the ID, then insert into your primary and use the ID you just retrieved.