Re: Need help with 'unique parents' constraint

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/