Re: Need help with 'unique parents' constraint - Mailing list pgsql-sql

From Leif B. Kristensen
Subject Re: Need help with 'unique parents' constraint
Date
Msg-id 200509111459.28037.leif@solumslekt.org
Whole thread Raw
In response to Need help with 'unique parents' constraint  ("Leif B. Kristensen" <leif@solumslekt.org>)
List pgsql-sql
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/


pgsql-sql by date:

Previous
From: "Leif B. Kristensen"
Date:
Subject: Need help with 'unique parents' constraint
Next
From: "Greg Sabino Mullane"
Date:
Subject: Re: Need help with 'unique parents' constraint