From: "Leif B. Kristensen" <leif@solumslekt.org>
To: pgsql-sql@postgresql.org
Subject: Need help with 'unique parents' constraint
Date: Sun, 11 Sep 2005 14:24:03 +0200
User-Agent: KMail/1.8.1
MIME-Version: 1.0
Content-Type: text/plain;
  charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline
Message-Id: <200509111424.04331.leif@solumslekt.org>

This message has also been posted to comp.databases.

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=20
        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=3D1) and one mother (gender=3D2). (Yes, this is old-
fashioned, but I'm working with 18th century people). How do I do it?

I have tried this:

ALTER TABLE relations ADD CONSTRAINT non_unique_father
    CHECK (NOT EXISTS
        (SELECT persons.person_id, relations.parent_fk
            FROM persons AS P, relations AS R
            WHERE R.parent_fk =3D P.person_id
            AND P.gender =3D 1));

But psql replies with:

pgslekt=3D> \i install/add_unique_father_and_mother_constraint.sql
psql:install/add_unique_father_and_mother_constraint.sql:9: NOTICE: =20
adding missing FROM-clause entry in subquery for table "persons"
psql:install/add_unique_father_and_mother_constraint.sql:9: ERROR: =20
cannot use subquery in check constraint

=46rom what I've found on Google, it looks like the "cannot use subquery=20
in check constraint" is a real limitation in PostgreSQL. Can I use a=20
trigger to achieve what I want? I'm still a little shaky on triggers=20
and what they can do, having quite recently converted to PostgreSQL=20
from a certain Swedish dinky-db.
=2D-=20
Leif Biberg Kristensen
http://solumslekt.org/