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
Message-ID: <200509111459.28037.leif@solumslekt.org> (raw)
In-Reply-To: <200509111424.04331.leif@solumslekt.org>
References: <200509111424.04331.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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: leif@solumslekt.org
Subject: Re: Need help with 'unique parents' constraint
In-Reply-To: <200509111459.28037.leif@solumslekt.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox