How do I insert primary key value from one table to foreign key column in another?

Question

I am new to SQL and programming in general so please bear with me if the answer to this is obvious. I have one table called "tickets" which contains the primary key ticket_id. I also have another table called "contact_info" which stores the contact information of the person who created the ticket. In this table, ticket_id is a foreign key called ticket_number. The user inserts tickets through a GUI and the ticket_number is auto incremented in the database. How do I select the ticket_number and insert it into the row that contains the contact_info of the person who created that ticket?

This is the code right now and it does not do what I expect it to:

try{
              //Open a connection
              System.out.println("Connecting to a selected database...");
              this.connection = DriverManager.getConnection(url, username, password);
              System.out.println("Connected database successfully...");

              //Execute a query
              System.out.println("Inserting records into the table...");
              PreparedStatement pstmt = connection.prepareStatement(" INSERT INTO s_fuse_ticket_table "
                              + " (summary, status, severity, classification, type, internal_notes, description, assignees) "
                              + " VALUES (?, ?, ?, ?, ?, ?, ?, ?)");

                          if(summary.getText().equals("")){
                              throw new SQLException("Summary cannot be blank");
                          }
                          pstmt.setString(1, summary.getText());
                  pstmt.setString(2, status.getSelectionModel().getSelectedItem().toString());
                  pstmt.setString(3, severity.getSelectionModel().getSelectedItem().toString());
                          if(classification.getSelectionModel().getSelectedItem().toString().equals("Make a Selection")){
                              throw new SQLException("Please select a classification");
                          }
                          pstmt.setString(4, classification.getSelectionModel().getSelectedItem().toString());
                          if(type.getSelectionModel().getSelectedItem().toString().equals("Make a Selection")){
                              throw new SQLException("Please select a type");
                          }
                          pstmt.setString(5, type.getSelectionModel().getSelectedItem().toString());
                          pstmt.setString(6, internalNotes.getText());
                          pstmt.setString(7, description.getText());
                          pstmt.setString(8, assignee.getSelectionModel().getSelectedItem().toString());
                  pstmt.executeUpdate();

                          //Execute a query
                        System.out.println("Inserting records into the table...");
                        PreparedStatement pstmt2 = connection.prepareStatement(" INSERT INTO s_fuse_contact_info_table "
                              + " (ticket_number, email, last_name, first_name) "
                              + " VALUES (?, ?, ?, ?)");

                          pstmt2.setString(1, ("SELECT ticket_id FROM s_fuse_ticket_table"));
                          /*if(!email.getText().contains("@") && !email.getText().contains(".")){

                          }*/
                          pstmt2.setString(2, email.getText());
                          pstmt2.setString(3, lname.getText());
                          pstmt2.setString(4, fname.getText());
                  pstmt2.executeUpdate();
           }catch(SQLException se){
              //Handle errors for JDBC
              se.printStackTrace();
           }catch(Exception e){
              //Handle errors for Class.forName
              e.printStackTrace();
           }finally{
              //finally block used to close resources
              try{
                 if(stmt!=null)
                 {
                    connection.close();
                 }
              }catch(SQLException se){
              }// do nothing
              try{
                 if(connection!=null)
                 {
                    connection.close();
                 }
              }catch(SQLException se){
                 se.printStackTrace();
              }//end finally try
           }//end try
           System.out.println("Goodbye!");

GHWP GHWP · Accepted Answer · 2016-11-29T15:50:10

Different DBMS' have different ways to do this - refer to this question for a pretty comprehensive list.

Another way is to use transactions. With an MVCC DBMS in a suitable isolation level (repeatable read or serializable), when you start a new connection you will have your own view of the db, so you can INSERT INTO the main table, then SELECT the latest row that matches your insert and be confident that it belongs to you. It's a good idea to bundle inserts like this together anyway, as it prevents any inconsistent data getting into the system - just make sure you commit afterward!

How do I insert primary key value from one table to foreign key column in another?

1 Answers