什么是用两个表填充需要互相引用的记录的最佳方法？

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?

在连接到mySQL数据库并保持完整性的同时,如何在PHP中执行此操作?

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

您是否需要使用交易,如果是这样,您将如何进行此操作?

3 个解决方案

#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.

我可以问为什么表格需要互相引用。如果它是直的1:1那么我建议只将外键放在其中一个表中。查找同样简单。并且您的外键可以正确执行。否则你会遇到鸡/蛋的情况,表格都需要互相参考。但需要首先创建一个。这意味着在某个时刻您的数据库将处于非一致状态。

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

#3

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

您需要使用交易。如果您使用InnoDb存储引擎(但不适用于MyIsam),MySql在较新版本中支持此功能。

#1

#2