MySQL database development. Insert rows in tables 1to1.

Question

What is the best way to insert rows into tables with links from 1 to 1 from each other?

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

The problem occurs when we try to insert rows in table1 and table2. Since MySQL does not have an insert for multiple tables, I cannot insert a row because foreign keys are not NULL fields in both tables and must be inserted at the same time in both.

What is the way to solve this problem?

I mean 3 possible solutions, but I want to know if there are more of them or what is the best and why.

• Set the foreign key field to NULLABLE and after inserting one row into the table, insert another and then update the first.

• As stated above, but with a special value, for example -1. First insert in the same table with foreign key = -1 , which is equivalent to NULL, but avoid setting the field as NULLABLE. After that, we insert the row into another table and update the first insert.

• Create a relational table between them, although this is not necessary, because it is a 1 to 1 ratio

Thanks!!

EDIT I will briefly explain what I need in this circular relationship: this is the denormalization from the parent table to one of its children. This is done in high performance order to always reference the best ranked child from the parent table.

Answer 1

I will answer this question, because I believe that this is a design error.

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

---

Secondly, if this is not a true 1:1 relationship, but a supertype-subtype problem, you also do not need these circular foreign keys. Suppose table1 is Employee and table2 is Customer . Of course, most customers are not employees (and vice versa). But sometimes the client can be an employee. This can be solved by 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 are describing, there are two tables, Parent and Child , with a ratio of 1:N Then you want to keep the child element for each parent as efficient as possible (based on a specific calculation).

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

Thus, you perform the required referential integrity (each BestChild is a child, each parent has only one BestChild), and the links do not have a round trip. The reference to the best child is stored in an additional table, not in the Parent table.

You can find BestChild for each parent by joining it:

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

---

In addition, if you want to store the best children for several performance tests (for different types of tests or tests on different dates), you can add the test field and change 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) 

Answer 2

I would create a black hole table and turn on the trigger to take care of the inserts

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

and put a trigger to take care of the 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 ; 

Please note that trigger actions are part of a single transaction (when using InnoDB or similar), so an error in the trigger will partially cancel partial changes.

Note on table structure
Please note that if it is a 1 by 1 table, you only need to put table2_id in table1, and table1_id in table2 (or vice versa) will not. If you need to query table1 based on table2, you can simply use:

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

Similarly for another way

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

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

Answer 3

I think this is an important question and I have not found a single 100% satisfactory answer on the Internet. The 2 answers you gave are the best I found, but they are not 100% satisfactory.

That's why:

• The reason Emilio cannot put his best child in the parent table is pretty simple, I suppose, because I have the same problem: not every child will be marked as the parent best child. Therefore, he will still need to store information about other children in another place. In this case, he will have some information about the best children in their parent table and other children in a separate database. This is a huge mess. For example, on the day when he wants to change the structure of the data on children, he needs to change it in both tables. Each time he writes a query for all the children, he must query both tables, etc.

• the reason why Emilio cannot just set the best child key for the child to nullable (I suppose for Emilio, but for me it would be very strict) is that he needs to be sure that the parent always has the best child . In the case of Emilio, this may not be very easy to imagine, but in my case I cannot have the equivalent of a parent who does not have a child.

So, I would be inclined to think that a solution with setting foreign_key_checks to zero would be better, but here is the problem:

• after setting foreign_key_checks to 1, there is no data integrity check. Thus, you run the risk of making mistakes at the same time. You can assume that you will not do this, but still this is not a very clean solution.