Oracle cyclic foreign key references issues

An alternative to deferrable constraints is a third table of (husband, wife) with two unique constraints (one on husband, one on wife), and have referential integrity constraints between that and the husbands table and wifes table. The wife/husband columns on the husbands/wifes tables would be redundant and should be dropped.



PS. Should it be WIVES rather than WIFES ?

Deferrable constraints are the right way to do it. Interestingly, there is an alternative way however -- with your setup and Oracle 10gR2:



SQL> CREATE OR REPLACE TRIGGER husband_wife_trg AFTER INSERT ON husbands
2 FOR EACH ROW
3 BEGIN
4 INSERT INTO wives VALUES (:new.wife, :new.name);
5 END;
6 /

Trigger created

SQL> INSERT INTO husbands VALUES ('Husband A', 'Wife B');

1 row inserted

SQL> SELECT * FROM wives;

NAME HUSBAND
---------- ----------
Wife B Husband A


I don't like putting transactional logic into triggers, but if you follow this path you don't need deferrable constraints.

You need a third table, not only for fixing this, but also for properly handling polygamy/bigamy which is legal in over 40 countries of the world.

Study deferrable constraints (not a new type, just a param to the existing ones), so far you did good.

Sorry - most answers are not adressing the exact problem at hand:



"MUST HAVE ONE AND ONLY ONE"



This essentially implies: YOU CAN NOT INSERT A SINGLE PERSON into the Database!!!
*Because a single Person would not have exactly one partner!



So the only valid solutions are:




  1. Deferrable Constraints: Easy as it can be - just mark your Constraints deferrable and then insert a wife and a husband and it will only check for integrity after commit (I don't know what people are complaining about - this is not cheating or strange... It is common practice and the only way in many commercial cases!!!)


  2. INSERT ALL - at least with newer Oracle Versions you can use the INSERT ALL Statement, which will insert into multiple tables at once. So you can write a single "Insert wife and hsuband" which is a pissibility for many use-cases.


  3. Trigger: In this special case a Trigger would do the trick - but as soon as you have additional attributes it wouldn't work anymore...




But all other answers were simply wrong for the proposed problem: Two objects with a mandatory 1 to 1 connection

1)setAutoCommit() as false
2)Inserts record into both table in one Unit Of Work.
3)commit

Silly idea - why not just have a single table "Couples" with columns "Husband_Name" and "Wife_Name" that each have a unique constraint? Seems to me like this satisfies all the requirements. :)

The need to use deferrable constraints is often a pointer to design problems. Certainly this data model is not a good one: it is not properly normalised. A normalised solution would look like this:



PERSON
------
ID number
NAME varchar2(30)
PRIMARY KEY (ID)


MARRIED_COUPLE
--------------
PARTNER_1 number
PARTNER_2 number
PRIMARY KEY (PARTNER_1, PARTNER_2)
FOREIGN KEY (PARTNER_1) REFERENCES (PERSON.ID)
FOREIGN KEY (PARTNER_2) REFERENCES (PERSON.ID)


This has the added advantage of supporting civil partnerships :) If you want to discourage bigamy then you could put unique keys on PARTNER_1 or PARTNER_2.



It is trickier to model cultures where polygyny or polyandry is permitted.



edit



What David is objecting to (in the comments) is this:



SQL> create table married_couple (partner_1 number, partner_2 number)
2 /

Table created.

SQL> alter table married_couple add primary key (partner_1, partner_2)
2 /

Table altered.

SQL> insert into married_couple values (1, 2)
2 /

1 row created.

SQL> insert into married_couple values (2,1)
2 /

1 row created.

SQL>


It's a valid point but it is resolvable. For instance, with Oracle I can create a unique function-based to enforce uniqueness of permutations.



SQL> delete from married_couple
2 /

2 rows deleted.

SQL> create unique index mc_uidx on married_couple
2 (greatest(partner_1, partner_2),least(partner_1, partner_2))
3 /

Index created.

SQL> insert into married_couple values (1, 2)
2 /

1 row created.

SQL> insert into married_couple values (2,1)
2 /
insert into married_couple values (2,1)
*
ERROR at line 1:
ORA-00001: unique constraint (APC.MC_UIDX) violated


SQL>


To avoid polygamy we can use a similar trick. We don't want this:



SQL> insert into married_couple values (1,3)
2 /

1 row created.


So, we need two indexes:



SQL> delete from married_couple where partner_2 = 3;

1 row deleted.

SQL> create unique index mc1_uidx
2 on married_couple (greatest(partner_1, partner_2))
3 /

Index created.

SQL> create unique index mc2_uidx
2 on married_couple (least(partner_1, partner_2))
3 /

Index created.

SQL> insert into married_couple values (3, 1)
2 /
insert into married_couple values (3, 1)
*
ERROR at line 1:
ORA-00001: unique constraint (APC.MC2_UIDX) violated


SQL>


To those who think it's cheating to solve a data modelling issue with an implementation trick, I plead "Guilty as charged" but I have had a long and trying day of it.


More: