Writing data to tables with relationship in SQL Server

Question

I have two different tables with multiple columns. Like the following

Table1

ID  CUSTOMER  PRODUCT DETAILS
21    joe         phone
22    doe         shoe
23    john        cup       

Table2

PRODUCT ID   COST    ID
   1         9000    21
   2          600    22  
   3          30     23

ID in table1 is primary index and PRODUCT ID in table2 is the primary index. The common link between these two tables is ID. I used the following query to read the values.

String query = "SELECT * FROM Table1 c1 INNER JOIN Table2 c2 ON c1.ID = c2.ID";

But my problem is when I am adding new row, I use the following queries for it.

String query1 = "INSERT INTO Table1(CUSTOMER) VALUES ('" + alex + "') ";
String query2 = "INSERT INTO Table2(COST) VALUES ('" + 500 + "') ";

Since ID is not the primary index for Table2, I get a NULL value in ID. When I try to read again, since ID are not matched the I cannot reload the saved data.

Edit

This is what I have tried based on the explanation given here,

String foreignKey = "ALTER TABLE Table2 ADD FOREIGN KEY (ID) REFERENCES Table1(ID)";

Still the column ID is null in table2. Do I need combine the queries in some sort to acheive the result ? Because I just execute queries one by one as follows.

Statement stmt = connect.createStatement();
stmt.executeUpdate(query1);
stmt.executeUpdate(query2);
stmt.executeUpdate(foreignKey);

Answer 1

General idea here - you need to get autogenerated id from first "insert" to Table1 and set "ID" at Table2 explicitly with this id in the second "insert". You can see how to do that here for example.

Foreign key in your second table - is just a constraint, that helps to prevent data's inconsistency. Its main purpose - is to give some message to you when you're trying to put some data with foreign key that doesn't exist in primary table. Also it can help (if you set this while creating foreign key) to remove from Table2 rows linked on foreign key with Table1 when you delete row at Table1.

And you shouldn't call stmt.executeUpdate(foreignKey); each time you insert data to Table1, Table2. It should be done once when you describe the structure of your database.