MySQL数据库设计。在1to1表中插入行。

What is the best way to insert rows into tables with references 1 to 1 of each other?

将行插入到彼此引用为1对1的表中的最佳方法是什么?

I mean, in a MySQL 5.5 and tables InnoDB, I have a database design similar to the following MySQL数据库设计。在1to1表中插入行。

我的意思是,在MySQL 5.5和表InnoDB中,我有一个类似于以下的数据库设计

The problem arises when we try to insert rows in table1 and table2. Since there is no multi-table insert in MySQL, I can not insert a row becouse the foreign keys are NOT NULL fields in both tables and should be inserted simultaneously in both.

当我们尝试在table1和table2中插入行时出现问题。由于MySQL中没有多表插入,因此我不能插入一行,因为外键在两个表中都是非NULL字段,并且应该同时插入两个表中。

Which is the bes way to solve this problem?

哪个是解决这个问题的方法?

I have in mind 3 possible solutions, but I want to know if there are more than these or which is the best and why.

我想到了3个可能的解决方案,但我想知道是否有更多这些或哪个是最好的以及为什么。

Set the foreign key field as NULLABLE and after insert one row in a table, insert the other one and afterwards, update de first one.

将外键字段设置为NULLABLE,并在表中插入一行后插入另一行,然后更新de first。
Just as indicated above but with an special value like -1. First, insert in one table with foreign key = -1 that is equivalent to NULL but avoiding set the field as NULLABLE. Afterwards, we insert the row in the other table and update the first one inserted.

正如上面所说,但是具有特殊值,如-1。首先,在一个表中插入外键= -1,该表等效于NULL,但避免将该字段设置为NULLABLE。然后,我们在另一个表中插入行并更新插入的第一行。
Create a relational table between both though it is not really necessary because it is a 1 to 1 ratio

在两者之间创建关系表,虽然它不是真正必要的,因为它是1比1的比率

Thanks!!

EDIT I briefly explain what I need this circular relationship: It is a denormalization from the parent table to one of its childs. It is made in order of high performance to have always the reference of the best ranked child from a parent table.

编辑我简要解释一下我需要这种循环关系:它是从父表到其子节点之间的非规范化。按照高性能的顺序,它始终是父表中排名最高的子项的引用。

3 个解决方案

#1

I'll make this an answer as I feel this is a design flaw.

我会做出这个答案,因为我觉得这是一个设计缺陷。

First, if the two tables are in true 1:1 relationship, why don't you just have one table?

首先,如果这两个表是真正的1:1关系,你为什么不只有一个表?

Second, if it's not a true 1:1 relationship but a supertype-subtype problem, you don't need this circular foreign keys either. Lets say table1 is Employee and table2 is Customer. Off course most customers are not employees (and vice-versa). But sometimes a customer may be an employee too. This can be solved having 3 tables:

其次,如果它不是真正的1:1关系而是超类型 - 子类型问题,那么您也不需要这个循环外键。让我们说table1是Employee而table2是Customer。当然,大多数客户不是员工(反之亦然)。但有时客户也可能是员工。这可以通过3个表来解决:

Person
------
id
PRIMARY KEY: id

Employee
--------
personid
lastname
firstname
... other data
PRIMARY KEY: personid
FOREIGN KEY: personid
    REFERENCES Person(id)

Customer
--------
personid
creditCardNumber
... other data
PRIMARY KEY: personid
FOREIGN KEY: personid
    REFERENCES Person(id)

In the scenario you describe you have two tables Parent and Child having 1:N relationship. Then, you want to store somehow the best performing (based on a defined calculation) child for every parent.

在您描述的场景中,您有两个表Parent和Child具有1:N关系。然后,您希望以某种方式存储每个父级的最佳性能(基于定义的计算)子级。

Would this work?:

这会有用吗?:

Parent
------
id
PRIMARY KEY: id

Child
-----
id
parentid
... other data
PRIMARY KEY: id
FOREIGN KEY: parentid
    REFERENCES Parent(id)
UNIQUE KEY: (id, parentid)             --- needed for the FK below

BestChild
---------
parentid
childid
... other data
PRIMARY KEY: parentid
FOREIGN KEY: (childid, parentid)
    REFERENCES Child(id, parentid)

This way, you enforce the wanted referential integrity (every BestChild is a Child, every Parent has only one BestChild) and there is no circular path in the References. The reference to the best child is stored in the extra table and not in the Parent table.

这样,您可以强制执行所需的参照完整性(每个BestChild都是一个Child,每个Parent只有一个BestChild),并且References中没有循环路径。对最佳子项的引用存储在额外表中,而不是父表中。

You can find BestChild for every Parent by joining:

您可以通过加入为每个家长找到BestChild:

Parent
  JOIN BestChild
    ON Parent.id = BestChild.parentid
  JOIN Child
    ON BestChild.childid = Child.id

Additionally, if you want to store best children for multiple performance tests (for different types of tests, or tests in various dates), you can add a test field, and alter the Primary Key to (test, parentid):

此外,如果要为多个性能测试存储最佳子级(针对不同类型的测试或不同日期的测试),可以添加测试字段,并将主键更改为(test,parentid):

BestChild
---------
testid
parentid
childid
... other data
PRIMARY KEY: (testid, parentid)
FOREIGN KEY: (childid, parentid)
    REFERENCES Child(id, parentid)
FOREIGN KEY: testid
    REFERENCES Test(id)

#2

I'd create a blackhole table and put a trigger on that to take care of inserts

我会创建一个黑洞桌并在其上放置一个触发器来处理插入物

CREATE TABLE bh_table12 (
  table1col varchar(45) not null,
  table2col varchar(45) not null
) ENGINE = BLACKHOLE

and put a trigger on that to take care of inserts

并在其上设置一个触发器来处理插入物

DELIMITER $$

CREATE TRIGGER ai_bh_table12_each AFTER INSERT ON bh_table12 FOR EACH ROW
BEGIN
  DECLARE mytable1id integer;
  DECLARE mytable2id integer;

  SET foreign_key_checks = 0;
    INSERT INTO table1 (table1col, table2_id) VALUES (new.table1col, 0);
    SELECT last_insert_id() INTO mytable1id;
    INSERT INTO table2 (table2col, table1_id) VALUES (new.table2col, table1id);
    SELECT last_insert_id() INTO mytable2id;
    UPDATE table1 SET table2_id = mytable2id WHERE table1.id = mytable1id;
  SET foreign_key_checks = 1;
END $$

DELIMITER ;

Note that actions in a trigger are part of one transaction (when using InnoDB or likewise), so an error in the trigger will rollback partial changes.

请注意,触发器中的操作是一个事务的一部分(当使用InnoDB或类似事件时),因此触发器中的错误将回滚部分更改。

Note on your table structure
Note that if it's a 1-on-1 table, you only need to put a table2_id in table1 and no table1_id in table2 (or visa versa).
If you need to query table1 based on table2 you can just use:

关于表结构的注意事项请注意,如果它是1-on-1表,则只需要在table1中放置table2_id而在table2中不放入table1_id(反之亦然)。如果你需要根据table2查询table1,你可以使用:

SELECT table1.* FROM table1
INNER JOIN table2 on (table2.id = table1.table2_id)
WHERE table2.table2col = 'test2'

Likewise for the other way round

反之亦然

SELECT table2.* FROM table2
INNER JOIN table1 on (table2.id = table1.table2_id)
WHERE table1.table1col = 'test1'

Links:
http://dev.mysql.com/doc/refman/5.1/en/blackhole-storage-engine.html
http://dev.mysql.com/doc/refman/5.1/en/triggers.html

链接:http://dev.mysql.com/doc/refman/5.1/en/blackhole-storage-engine.html http://dev.mysql.com/doc/refman/5.1/en/triggers.html

#3

I feel this is an important question, and I haven't found any 100% satisfying answer throughout the web. The 2 answers that you have given are the best ones I found, yet they are not 100% satisfactory.

我觉得这是一个重要的问题,我没有在整个网络上找到任何100%令人满意的答案。你给出的2个答案是我找到的最好的答案,但它们并不是100%令人满意。

Here's why :

原因如下:

The reason why Emilio cannot put his best child inside his parent table is pretty simple, I presume, because I share the same problem : not every child will be labelled as a parent's best child. So he would still need to store information on other children somewhere else. In that case, he would have some information about the best children in their parent's table, and other children in a separate database. This is a huge mess. For example, the day he wants to change the data structure about children, he needs to change it in both tables. Every time he writes a query on all children, he should query both tables, etc...

我认为Emilio不能把他最好的孩子放在他父母的桌子里的原因很简单,因为我有同样的问题:不是每个孩子都会被贴上父母最好的孩子的标签。所以他仍然需要将信息存储在其他地方的其他孩子身上。在这种情况下,他会得到一些关于父母表中最好的孩子以及其他孩子的信息。这是一个巨大的混乱。例如,在他想要更改有关子项的数据结构的那一天,他需要在两个表中更改它。每次他对所有孩子写一个查询,他都应该查询两个表等...
the reason why Emilio cannot just set the best child foreign key to nullable (I presume for Emilio, but for me it would be very strict), is that he needs to be sure that a parent always has a best child. In Emilio's case it's maybe not very easy to imagine, but in mine, I cannot have the equivalent of the parent have no child.

为什么Emilio不能把最好的子外键设置为可以为空(我为Emilio设定,但对我来说这将是非常严格的),是因为他需要确保父母总是拥有一个最好的孩子。在埃米利奥的情况下,它可能不是很容易想象,但在我的情况下,我不能拥有相当于父母没有孩子的东西。

Thus I would have tended to think that the solution with setting foreign_key_checks to zero would be best, but here is the problem :

因此,我倾向于认为将foreign_key_checks设置为零的解决方案是最好的,但问题是:

after setting foreign_key_checks back to 1, there is no check on data's consistency. Thus, you have a risk of making mistakes in the meantime. You can consider that you won't, but still it is not a very clean solution.

将foreign_key_checks设置回1后,没有检查数据的一致性。因此,在此期间您有犯错的风险。你可以认为你不会,但它仍然不是一个非常干净的解决方案。

#1