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.