Re: foreign keys constraints, depending on each other

On Sun, 10 Jun 2001 zilch@home.se wrote:

> I was about the create 3 tables, namely mother, father and child. Mother has
> a foreign key pointing at father ( id ), and father has a foreign key
> pointing at mother ( id ). Child has one pointer to mother ( id ) and one
> pointer to father ( id ). How can I prevent the error message from occurring?

Personnally, I tend to avoid those circular references in any computing
field.

I would remove the father and mother references, and add a
   is_married
relation; as a table, with a UNIQUE(father_id), UNIQUE(mother_id)
constraint (a person can be only married once).

I would keep the direct references from child.

It might a bit diminush the performance, but circular references are a
pain to handle.

Alternatively, keep only the mother -> father reference, and determine the
wife of father through query like:

   SELECT m.id FROM mother m WHERE m.father_id = ?

This can be quite efficient if the mother was looked up previously anyway.