From: "Leif B. Kristensen" <leif@solumslekt.org>
To: pgsql-sql@postgresql.org
Subject: Re: Need help with 'unique parents' constraint
Date: Sun, 11 Sep 2005 14:59:27 +0200
User-Agent: KMail/1.8.1
References: <200509111424.04331.leif@solumslekt.org>
In-Reply-To: <200509111424.04331.leif@solumslekt.org>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200509111459.28037.leif@solumslekt.org>

On Sunday 11 September 2005 14:24, Leif B. Kristensen wrote:

> 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 = P.person_id
>             AND P.gender = 1));

Forget this. Please pretend that you never saw it in the first place :-)

I've done some experimenting:

pgslekt=> alter table relations add column rel_type smallint
pgslekt-> not null default 0 check (rel_type in (0,1,2,9));
ALTER TABLE
pgslekt=> update relations set rel_type = (select gender from
pgslekt(> persons where person_id = parent_fk);
UPDATE 20012
pgslekt=> select * from relations where child_fk=1;
 relation_id | child_fk | parent_fk | rel_memo | rel_type
-------------+----------+-----------+----------+----------
           3 |        1 |         2 |          |        1
           4 |        1 |         3 |          |        2
(2 rows)

pgslekt=> alter table relations add constraint unique_parent
pgslekt-> unique (child_fk,rel_type);
NOTICE:  ALTER TABLE / ADD UNIQUE will create implicit index 
"unique_parent" for table "relations"
ALTER TABLE

And this is more or less what I want. But I don't like the redundant 
relations.rel_type column.
-- 
Leif Biberg Kristensen
http://solumslekt.org/