MySQL Database design. Inserting rows in 1to1 tabl

问题:

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

I mean, in a MySQL 5.5 and tables InnoDB, I have a database design similar to the following

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.

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.

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

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

3. Create a relational table between both though it is not really necessary because it is a 1 to 1 ratio

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.

回答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?

---

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:

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.

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.

You can find BestChild for every Parent by joining:

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):

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.

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:

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

回答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.

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

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

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 :

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