7

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.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
brain storm
  • 30,124
  • 69
  • 225
  • 393
  • possible duplicate of [How to get the insert ID in JDBC?](http://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc) – Mark Rotteveel Sep 18 '14 at 19:15
  • @MarkRotteveel: that link was helpful in clarifying the first part. with regards to second part of getting ids for join table, is there a way in JDBC that does the inserts automatically. or a trigger is needed at database end. if so, for triggers to work, one needs to know the id of both rows in both tables – brain storm Sep 18 '14 at 19:22
  • This is one of the reasons why you should stick to one specific question on SO. However, MySQL has triggers, and you can get values from the inserted record to insert into another table, see http://dev.mysql.com/doc/refman/5.7/en/trigger-syntax.html (specific example at end) – Mark Rotteveel Sep 18 '14 at 19:33
  • BTW: It is unclear to me why you need that 'join table'; it is unnecessary given the fact that the salary table already includes the employee id. – Mark Rotteveel Sep 18 '14 at 19:36
  • @MarkRotteveel: I agree that it is unnecessary here. but I my intention was to provide an example to make my point clear. And I am not sure if trigger is the best practice to get join table insertions or is it better to perform at JDBC level (in which case you can roll back and handle it appropriately) – brain storm Sep 18 '14 at 19:41
  • I don't use MySQL so I don't know its ins and outs, but I'd assume that a rollback will also rollback inserts done from a trigger (assuming you are using InnoDB). – Mark Rotteveel Sep 18 '14 at 19:44
  • @MarkRotteveel: forget the MySQL part for the moment. At the JDBC level, how to make insertion of join table happen? what is the best practice here? – brain storm Sep 18 '14 at 19:56
  • Code it: Insert employee; obtain id, insert salary; obtain id, insert join table. The fact it is a 'join table' is irrelevant, the steps are the same as when you need to obtain the employee id for inserting into salary. – Mark Rotteveel Sep 18 '14 at 19:58
  • JDBC is a client, so you can't forget the MySQL part. – Gervs Sep 18 '14 at 19:58
  • @MarkRotteveel: I now figured out how to get it done. But I want to know if there is any best practice here. For example, for each insertion I get the generated_keys and use it in next insert in the table. I am thinking if there is a consensus in the community of how uniformly it is done. – brain storm Sep 18 '14 at 20:03

2 Answers2

6

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;
VMai
  • 10,156
  • 9
  • 25
  • 34
  • how the `LAST_INSERT_ID()` get the last inserted key from employee table. you don't specify table name. I guess my question is different. please have a read again. It has to deal with foreign key references – brain storm Sep 18 '14 at 19:13
  • 2
    @brainstorm Simply: `LAST_INSERT_ID()` returns the last generated id in your session. So if that `INSERT` generated an id, that id will be returned (assuming you don't execute other inserts in between). However, it is not the 'JDBC' way of obtaining generated keys, see http://stackoverflow.com/questions/1915166/how-to-get-the-insert-id-in-jdbc/1915197 – Mark Rotteveel Sep 18 '14 at 19:15
  • @MarkRotteveel You're absolutely right about the "JDBC" way, because such an API will do an abstraction of the DBMS function, so it can work with any database management system. In absence of any java code I wrote the SQL way ... – VMai Sep 18 '14 at 19:22
  • @VMai: how would get the ids to be inserted for the join table for trigger to happen? – brain storm Sep 18 '14 at 19:24
  • Just like you would handle any other value. – Gervs Sep 18 '14 at 19:29
  • @Gervs: and how is that? please give an example if you can – brain storm Sep 18 '14 at 19:32
0

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);
                }
Touseef Butt
  • 1