Need a 'unique parents' constraint

I've got a problem that I can't quite wrap my head around, about adding
a constraint to my PostgreSQL genealogy database. If somebody are
interested, I've written some thoughts on the general design at
<http://solumslekt.org/forays/blue.php>.

I've got two tables, persons and relations. I need a separate relations
table for source referencing and discussion. Here are my preliminary
definitions (irrelevant columns removed):

CREATE TABLE persons (
person_id INTEGER PRIMARY KEY,
gender SMALLINT NOT NULL DEFAULT 0
CHECK (gender IN (0,1,2,9)), -- ISO gender codes
);

CREATE TABLE relations (
relation_id INTEGER PRIMARY KEY,
child_fk INTEGER REFERENCES persons (person_id),
parent_fk INTEGER REFERENCES persons (person_id),
CONSTRAINT child_parent UNIQUE (child_fk, parent_fk)
);

Now, I want to ensure that each person_id can be assigned only one
father (gender=1) and one mother (gender=2). (Yes, this is old-
fashioned, but I'm working with 18th century people). How do I do it?
--
Leif Biberg Kristensen
http://solumslekt.org/
.

Relevant Pages

  • Need a unique parents constraint
    ... a constraint to my PostgreSQL genealogy database. ... relation_id INTEGER PRIMARY KEY, ... parent_fk INTEGER NOT NULL REFERENCES persons, ... CONSTRAINT child_parent UNIQUE ...
    (comp.databases)
  • Re: Database design question
    ... doc_id INTEGER PRIMARY KEY ... CONSTRAINT fk_docid_docperm REFERENCES document, ... CONSTRAINT fk_groupid_docperm REFERENCES group, ... docperm are functionally dependent on doc_id? ...
    (comp.databases.theory)
  • Re: Database design question
    ... doc_id INTEGER PRIMARY KEY ... CONSTRAINT fk_docid_docperm REFERENCES document, ... CONSTRAINT fk_groupid_docperm REFERENCES group, ... Thanks Dan. ...
    (comp.databases.theory)