Oracle cyclic foreign key references issues

I've been racking my brain trying to come up with a solution to this.

For a database class, I need to implement the following:

Table HUSBANDS: (Name Varchar2(10)) (Wife Varchar2(10))
Table WIVES: (Name Varchar2(10)) (Husband Varchar2(10))

and using Oracle constraints, enfore the following rules:

1. No two husbands can have the same name
2. No two wives can have the same name
3. Every wife must have one and only one husband
4. Every husband must have one and only one wife

So far, I have implemented the table in Oracle SQL:

create table husbands(
  name varchar2(10) not null
  , wife varchar2(10) not null
);
create table wives(
  name varchar2(10) not null
  , husband varchar2(10) not null
);

I'm pretty sure I have solved points 1 and 2 using correct primary keys:

alter table husbands
  add constraint husbands_pk
  primary key(name);
alter table wives
  add constraint wives_pk
  primary key(name);

And here is where I'm running into issues. I figured to use foreign keys to implement steps 3 and 4:

alter table husbands
  add constraint husbands_fk_wife
  foreign key(wife)
  references wives(name);
alter table wives
  add constraint wives_fk_husband
  foreign key(husband)
  references husbands(name);

Now the test case my professor is using is to be able to add a married couple to 开发者_运维知识库the database. The problem I am having is how to do this using only constraints. If I wanted to add Jack and Jill as a married couple, one cannot add the husband until the wife is added. the wife cannot be added until a husband is added.

I think my problem is using foreign keys. A check constraint might work in this situation, but I cannot conceptualize how it would work.

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 ?

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.

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

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.

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

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

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.

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