title: Inserting mysql foreign keys and primary keys in a transaction.
body: <p>Just looking for some tips and pointers for a small project I am doing. I have some ideas but I am not sure if they are the best practice. I am using mysql and php.</p>
  
  <p>I have a table called nomsing in the database.</p>
  
  <p>It has a primary key called row id which is an integer.</p>
  
  <p>Then I have about 8 other tables referencing this table. 
  That are called nomplu, accsing,accplu, datsing, datplu for instance.
  Each has a column that references the primary key of nomsing. </p>
  
  <p>Withing my php code I have all the information to insert into the tables except one thing , the row id primary key of the nomsing table. So that php generates a series of inserts like the following.</p>
  
  <pre><code>INSERT INTO nomsing(word,postress,gender) VALUES (''велосипед","8","mask").
  INSERT INTO nomplu(word,postress,NOMSING?REFERENCE) VALUES (''велосипеды","2",@the reference to the id of the first insert@).
  </code></pre>
  
  <p>There are more inserts but this one gets the point across. The second insert should reference the auto generated id for the first insert. I was this to work as a transaction so all inserts should complete or none. </p>
  
  <p>One idea I have is to not auto generate the id and generate it myself in php. That way would know the id given before the transaction but then I would have to check if the id was already in the db.</p>
  
  <p>Another idea I have is to do the first insert and then query for the row id of that insert in php and then make the second insert. I mean both should work but they don't seem like an optimal solution. I am not too familiar with the database transactional features but what would be the best approach to do in this case. I don't like the idea of inserting then querying for the id and then running the rest of the queries. Just seems very inefficient or perhaps I am wrong.</p>