title: how to automatically insert foreign key references in tables in mysql or JDBC?
body: <p>I am using MySQL. My question is how to automatically insert the newly added row into a foreign-key table. An example will clarify my question:</p>
  
  <p>I have two tables Employee and Salary:</p>
  
  <pre><code>CREATE TABLE Employee(
       emp_id int NOT NULL AUTO_INCREMENT,
       name char(30),
       PRIMARY KEY (emp_id)
  )  ENGINE=innoDB;
  
  CREATE TABLE salary {
         sal_id int NOT NULL AUTO_INCREMENT
         salary_figure int,
         emp_id int,
         PRIMARY KEY (sal_id),
         FOREIGN KEY REFERENCES Employee(emp_id)
  }
  </code></pre>
  
  <p>Here is the join table :</p>
  
  <pre><code>employee_salary_join Table {
      int sal_id,
      int emp_id
  }
  </code></pre>
  
  <p>The join table above does not have any foreign key relationship. </p>
  
  <p>Now when I insert an employee into employee table</p>
  
  <pre><code>INSERT into Employee values ("john")
  </code></pre>
  
  <p>After this statement is executed, a row is created in Employee table that has a pk assigned by database engine.</p>
  
  <p>Now when I insert a row for employee John in salary table as below:</p>
  
  <pre><code>INSERT into SALARY values ("30000", ?????)
  </code></pre>
  
  <p>How do I get the employee ID of just inserted row for John above and put it here in the place of <code>?????</code></p>
  
  <p>Finally, I have a <em>join table</em>, where every time a row is added in salary table, I want the corresponding entry in the join table. This could be done by triggers, but I am not totally clear how to set it up because I need references of both <code>emp_id</code> and <code>sal_id</code> for the join table row.</p>
  
  <p>I would also like to know the best practices here when dealing with <em>foreign key inserts</em>. I am using JDBC/mysql driver. I guess that should not affect how we the process the above in sql statements.</p>