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
Message-ID: <200509111424.04331.leif@solumslekt.org> (raw)
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
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=1) and one mother (gender=2). (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 = P.person_id
AND P.gender = 1));
But psql replies with:
pgslekt=> \i install/add_unique_father_and_mother_constraint.sql
psql:install/add_unique_father_and_mother_constraint.sql:9: NOTICE:
adding missing FROM-clause entry in subquery for table "persons"
psql:install/add_unique_father_and_mother_constraint.sql:9: ERROR:
cannot use subquery in check constraint
From what I've found on Google, it looks like the "cannot use subquery
in check constraint" is a real limitation in PostgreSQL. Can I use a
trigger to achieve what I want? I'm still a little shaky on triggers
and what they can do, having quite recently converted to PostgreSQL
from a certain Swedish dinky-db.
--
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: <200509111424.04331.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