how to perform one-to-one relational insertion in database

Question

I am using Mysql. And I have two tables which are one-to-one related with each other.

In other words, they both have a foreign key constraint referencing the primary key of the other table.

If I try to insert one record for each table, in which each record references the other. like:

The Mysql database will prevent such operation, because of the one-to-one foreign key constraint.

It is like the chicken-egg problem.

However, I noticed that in Java hibernate, such operation can be done in case two entity classes are one-to-one related.

How can that be done in Hibernate, because I notice that the two insertions(from Hibernate debug messages) are separated as I have done. So there is nothing special.

Or how can I force one record to be inserted even if the record it should referencing does not yet exist?

What's the reason for a circular foreign key constraint? It may imply a bad design. — Mike, Jul 23 '11 at 11:52
I think am also facing similar problem. Any solution for this issue, please post. TIA — user883769, Aug 08 '11 at 10:08
This is a good question and had me wondering. sudmong's answer below is most likely how it's done with an ORM framework. — James P., Sep 01 '13 at 03:14

score 3 · Accepted Answer · answered Jul 23 '11 at 11:55

3

Put null value for one of the referenced columns, ref columns do allow null values.Later once you insert record in the referenced column, you can update the column.

answered Jul 23 '11 at 11:55

sudmong

2,036
13
12

Actually I noticed that hibernate did the same thing as you suggested, sudmong. – xiaohan2012 Jul 23 '11 at 12:03

score 1 · Answer 2 · answered Jul 23 '11 at 11:48

1

You could try to disable the foreign key checks by running SET foreign_key_checks = 0 immediately before your two insert querys and immediately after your querys, enable the foreign key checking by executing SET foreign_key_checks = 1.

More information about foreign key constraints.

mysql > SET foreign_key_checks = 0;
mysql > INSERT INTO t1 (t2_id) VALUES (1);
mysql > INSERT INTO t2 (t1_id) VALUES (1);
mysql > SET foreign_key_checks = 1;

answered Jul 23 '11 at 11:48

mAu

2,020
1
14
27

1

Disabling foreign key constraints is usually a very bad idea. If you do do it, then at least put the whole lot in a transaction. – Mike Jul 23 '11 at 11:52
Of course you should run it inside a transaction. But i don't see any other way to achieve the required without disabling the foreign key checks ... – mAu Jul 23 '11 at 11:55
Sorry, ignore it. I am mistaking something – xiaohan2012 Jul 23 '11 at 12:07

how to perform one-to-one relational insertion in database

2 Answers2