how to automatically insert foreign key references

Question

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:

I have two tables Employee and Salary:

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

Here is the join table :

employee_salary_join Table {
    int sal_id,
    int emp_id
}

The join table above does not have any foreign key relationship.

Now when I insert an employee into employee table

INSERT into Employee values ("john")

After this statement is executed, a row is created in Employee table that has a pk assigned by database engine.

Now when I insert a row for employee John in salary table as below:

INSERT into SALARY values ("30000", ?????)

How do I get the employee ID of just inserted row for John above and put it here in the place of ?????

Finally, I have a join table, 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 emp_id and sal_id for the join table row.

I would also like to know the best practices here when dealing with foreign key inserts. I am using JDBC/mysql driver. I guess that should not affect how we the process the above in sql statements.

Answer 1

You get this ID of your auto_increment column for the inserted row with the function LAST_INSERT_ID: So you can use

INSERT into SALARY (salary_figure, emp_id) values ("30000", LAST_INSERT_ID());

for your second INSERT operation.

If you want to add a row by a trigger into a third table, using the new sal_id and emp_id values, you can do that with an AFTER INSERT trigger on the Salary table using the new value in the column emp_id and the last inserted auto_increment id ... with already mentioned LAST_INSERT_ID() function.

CREATE TRIGGER salary_after_insert AFTER INSERT ON `SALARY` 
    FOR EACH ROW
    BEGIN
         INSERT INTO join_table (emp_id, sal_id) VALUES (NEW.emp_id, LAST_INSERT_ID());
    END;

Answer 2

I use this code and its working properly

 try 
        { 
        SqlCommand comm = new SqlCommand("Insert into tbl_sale(Terminal_NO, Date, Sale_Type, Fuel_Type, Unit) values('"+ddl_terminal.SelectedItem+"','"+dt_sales.Value.Date+"', '"+ddl_SaleType.SelectedItem+"', '"+ddl_FuelType.SelectedItem+"', '"+ddl_unit.SelectedItem+"')",conn);
SqlCommand com = new SqlCommand("Insert into tbl_saleDetails(Sale_ID, Unit_Sold, Amount_per_Liter) values((Select Sale_ID from tbl_sale where Sale_ID = (Select MAX(Sale_ID) from tbl_sale)),'" + txt_Cash_Unit_Sold.Text + "','" + txt_Cash_Amount_per_liter.Text + "')", conn);
        conn.Open();
        comm.ExecuteNonQuery();
        com.ExecuteNonQuery();
        conn.Close();
            MessageBox.Show("Data Saved Successfully!");
            }
        catch(Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }