Insert row in table B on table A's insert query

Using MySQL, I assum开发者_如何学编程ed there was a way to do it relationally but perhaps I'm wrong.

I have table A with userID, and table B with userID, it's set so on delete of A.userID to cascade and delete B.userID.

So I have a query (Using CI), inserting information in table A, I want to insert a row into table B with the same userID (PK, AI). Sometimes it will have values to insert in B.userID row, sometimes it will be entirely null values.

Thoughts on how to do this?

Thanks.

I would really need to see the actual query, but I believe this is what you're looking for.

When you insert data in CodeIgniter you can get the Primary Key of your insert like this.

$this->db->insert('A',$data);  //insert into the table with 'userID' as PK, AI

$id_of_insert = $this->db->insert_id();

$data_for_b = array(
    'field' => 'value',
    ...
    'userID' => $id_of_insert   //insert the captured PK from A and place in the FK field of B
);

$this->db->insert('B', $data_for_b);

The easiest thing to do would be to write a stored procedure to perform the insert. That could do both inserts within a transaction and accept parameters for the data to add into B as well as A. Then you query would call the stored proc instead of doing a straight insert.

There are a few things you should consider in this approach:

1. If your auto_increment is BIGINT it gives wrong values.

2. If you insert something and someone else inserted something as well before you read the value you might get the wrong id. I recommend reading the value with something different that is unique. (e.g. nickname in a user registration).