CODEWITHSUNDEEP
c#mysql
Ren
Ren

Reputation: 775

Auto generate id in child table when value insert in parent table

I have two table which is the parent and the child table. Now I insert a value to the parent table, with auto increment id. The code is something like this:

string query2 = "INSERT INTO printer_info(printer_ip) VALUES(@ipAddress)";
MySqlCommand cmd1 = new MySqlCommand(query2, connection);

cmd1.Parameters.AddWithValue("@ipAddress", pAddress);
cmd1.ExecuteNonQuery();

Now my problem is that when the value insert in the parent table. How if I want the id in the child table to be generated automatically?Please advise.

   ID | printer_ip               printer_id | page_count
   ---------------    ------>    -----------------------
    1 | 10.0.0.0                            |

   PRIMARY KEY ID                FOREIGN KEY printer_id

Upvotes: 1

Views: 924

Answers (2)

Ravinder Reddy
Ravinder Reddy

Reputation: 24012

If you want to insert a row in child too, just after parent row is created, then you can make use of last_insert_id() in the next insert statement.

Example: 

string query2 = "INSERT INTO printer_info(printer_ip) VALUES(@ipAddress)";
MySqlCommand cmd1 = new MySqlCommand(query2, connection);

cmd1.Parameters.AddWithValue("@ipAddress", pAddress);
cmd1.ExecuteNonQuery();

string query_for_child_row = "INSERT INTO printer_queue(printer_id, page_count) 
                              VALUES( LAST_INSERT_ID(), @page_count )";
MySqlCommand cmd2 = new MySqlCommand( query_for_child_row, connection );

cmd2.Parameters.AddWithValue( "@page_count", page_count );
cmd2.ExecuteNonQuery();

Upvotes: 2

st4hoo
st4hoo

Reputation: 2204

You could use insert trigger to add new row in child table when new row in parent table shows up.

EDIT

HINT: those two actions should be performed inside single transaction to avoid inconsistency in case one of them fails...

Upvotes: 1

Related Questions