1

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?

David Schmitt
  • 58,259
  • 26
  • 121
  • 165
Omar Kooheji
  • 54,530
  • 68
  • 182
  • 238

3 Answers3

3

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.

Jack Ryan
  • 8,396
  • 4
  • 37
  • 76
  • To be honest I'm asking SO because I colleague asked me and I thought it would be quicker to probe the collective hivemind. So I don't know what the context is. – Omar Kooheji Jan 26 '09 at 11:50
1

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");
Riho
  • 4,523
  • 3
  • 33
  • 48
0

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

troelskn
  • 115,121
  • 27
  • 131
  • 155