CODEWITHSUNDEEP
phpmysqlreferencereferential-integrity
Omar Kooheji
Omar Kooheji

Reputation: 55780

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

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?

Upvotes: 1

Views: 299

Answers (3)

troelskn
troelskn

Reputation: 117595

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

Upvotes: 0

Riho
Riho

Reputation: 4595

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");

Upvotes: 1

Jack Ryan
Jack Ryan

Reputation: 8472

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.

Upvotes: 3

Related Questions