Whats the best way to populate two tables with records that need to reference each other?

Question

What is the best way to populate records in two tables that each need a reference to the primary key of the other?

My thoughts are either having a "link" table between them which is populated once both have been written to the db or by the following complex series of commands

Insert1
get identity1
Insert2 
get identity2
update 1

How would you do this in PHP whilst connected to a mySQL Database and maintain integrity?

Would you need to use transactions, if so how would you go about doing this?

Answer 1

Can I ask why the tables need to reference each other. If it is a straight 1:1 then I would suggest just putting the foreign key in one of the tables. the lookup is just as easy. And your foreign key can be properly enforced. otherwise you run into a chicken/egg scenario where the tables both need to reference each other. but one needs to be created first. This means at a certain point your database will be in a non consistent state.

Answer 2

If you really must do it , then definitely use transactions - to avoid orphan records

mysql_query("START TRANSACTION");
if(!mysql_query($query1))
{   $errmsg=mysql_error();
    mysql_query("ROLLBACK");
}
else
{  $id1=mysql_insert_id();
   $query2="insert into.....$id1...");
   if(!mysql_query($query2))
   {   $errmsg=mysql_error();
       mysql_query("ROLLBACK");
   }
   $id2=mysql_insert_id();
   if(!mysql_query("update tabel1 set my_key=$id2 where key=$id1"))
   {   $errmsg=mysql_error();
       mysql_query("ROLLBACK");
   }

}
mysql_query("COMMIT");

Answer 3

You need to use transactions. MySql supports this in newer versions, if you use the InnoDb storage engine (But not for MyIsam).