pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNeed help with 'unique parents' constraint
7+ messages / 4 participants
[nested] [flat]
* Need help with 'unique parents' constraint
@ 2005-09-11 12:24 Leif B. Kristensen <leif@solumslekt.org>
0 siblings, 2 replies; 7+ messages in thread
From: Leif B. Kristensen @ 2005-09-11 12:24 UTC (permalink / raw)
To: pgsql-sql
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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help with 'unique parents' constraint
@ 2005-09-11 12:59 Leif B. Kristensen <leif@solumslekt.org>
parent: Leif B. Kristensen <leif@solumslekt.org>
1 sibling, 0 replies; 7+ messages in thread
From: Leif B. Kristensen @ 2005-09-11 12:59 UTC (permalink / raw)
To: 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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help with 'unique parents' constraint
@ 2005-09-11 14:04 Greg Sabino Mullane <greg@turnstep.com>
parent: Leif B. Kristensen <leif@solumslekt.org>
1 sibling, 2 replies; 7+ messages in thread
From: Greg Sabino Mullane @ 2005-09-11 14:04 UTC (permalink / raw)
To: pgsql-sql; +Cc: Leif B. Kristensen <leif@solumslekt.org>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> 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?
Not just old-fashioned, it's the biological law! (among homo sapiens anyway).
I'd approach this with a trigger, as you can do complex checks and get back
nice customized error messages. A sample script follows. Hard to tell without
seeing your whole schema, but I see no need for a relation_id primary key
if you already have a unique constraint on child_fk and parent_fk, so I
made those into the primary key for the relations table:
DROP TABLE relations;
DROP TABLE persons;
DROP FUNCTION relation_check();
DROP SEQUENCE persons_seq_id;
CREATE SEQUENCE persons_seq_id;
CREATE TABLE persons (
person_id INTEGER PRIMARY KEY NOT NULL DEFAULT nextval('persons_seq_id'),
gender SMALLINT NOT NULL DEFAULT 0
CHECK (gender IN (0,1,2,9))
);
COMMENT ON COLUMN persons.gender IS 'ISO Gender code 1=father 2=mother';
CREATE TABLE relations (
child_fk INTEGER REFERENCES persons (person_id) ON DELETE CASCADE,
parent_fk INTEGER REFERENCES persons (person_id) ON DELETE CASCADE,
PRIMARY KEY (child_fk, parent_fk)
);
CREATE FUNCTION relation_check() RETURNS TRIGGER LANGUAGE plpgsql AS
$$
DECLARE
xy SMALLINT;
trace INTEGER;
BEGIN
- -- Assume that child or parent has changed, since this version has no other columns
IF NEW.child_fk = NEW.parent_fk THEN
RAISE EXCEPTION 'Bioethics error: Human cloning not supported yet';
END IF;
SELECT gender FROM persons WHERE person_id = NEW.parent_fk INTO xy;
- -- More than one father?
IF xy = 1 THEN
SELECT parent_fk FROM relations r, persons p WHERE r.child_fk = NEW.child_fk
AND r.parent_fk = p.person_id AND p.gender = 1 INTO trace;
IF trace IS NOT NULL THEN
IF TG_OP = 'UPDATE' THEN
IF OLD.parent_fk != trace THEN
RAISE EXCEPTION 'Error: Cannot change parent: person % is already assigned as the father', trace;
END IF;
ELSE
RAISE EXCEPTION 'Error: Person % is already assigned as the father', trace;
END IF;
END IF;
END IF;
- -- More than one mother?
IF xy = 2 THEN
SELECT parent_fk FROM relations r, persons p WHERE r.child_fk = NEW.child_fk
AND r.parent_fk = p.person_id AND p.gender = 2 INTO trace;
IF trace IS NOT NULL THEN
IF TG_OP = 'UPDATE' THEN
IF OLD.parent_fk != trace THEN
RAISE EXCEPTION 'Error: Cannot change parent: person % is already assigned as the mother', trace;
END IF;
ELSE
RAISE EXCEPTION 'Error: Person % is already assigned as the mother', trace;
END IF;
END IF;
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER relation_check BEFORE INSERT OR UPDATE ON relations
FOR EACH ROW EXECUTE PROCEDURE relation_check();
INSERT INTO persons(gender) VALUES (1);
INSERT INTO persons(gender) VALUES (2);
INSERT INTO persons(gender) VALUES (1);
INSERT INTO persons(gender) VALUES (2);
INSERT INTO persons(gender) VALUES (0);
INSERT INTO persons(gender) VALUES (1);
INSERT INTO relations VALUES (3,1);
INSERT INTO relations VALUES (3,2);
SELECT 'Cloning test' AS "Test should fail";
INSERT INTO relations VALUES (3,3);
SELECT 'Change father to another mother' AS "Test should fail";
UPDATE relations SET parent_fk = 4 WHERE child_fk = 3 AND parent_fk = 1;
SELECT 'Add in a second father' AS "Test should fail";
INSERT INTO relations VALUES (3,6);
SELECT 'Change fathers' AS "Test should pass";
UPDATE relations SET parent_fk = 6 WHERE child_fk = 3 AND parent_fk = 1;
SELECT 'Change mother to another father' AS "Test should fail";
UPDATE relations SET parent_fk = 6 WHERE child_fk = 3 AND parent_fk = 2;
SELECT 'Add in a second mother' AS "Test should fail";
INSERT INTO relations VALUES (3,4);
SELECT 'Change mothers' AS "Test should pass";
UPDATE relations SET parent_fk = 4 WHERE child_fk = 3 AND parent_fk = 2;
SELECT 'Add non-mother/father' AS "Test should pass";
INSERT INTO relations VALUES (3,5);
SELECT 'Change non-mother/father to mother' AS "Test should fail";
UPDATE relations SET parent_fk = 2 WHERE child_fk = 3 AND parent_fk = 5;
SELECT * FROM relations;
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200509110958
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFDJDkzvJuQZxSWSsgRAryTAJ90oT0LWl2ch6c7T7tPsj1/+JpRFwCeOLsV
ceYzuVEHbZPjdCgaMCG65rQ=
=wh38
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help with 'unique parents' constraint
@ 2005-09-11 17:28 John Hasler <john@dhh.gt.org>
parent: Greg Sabino Mullane <greg@turnstep.com>
1 sibling, 0 replies; 7+ messages in thread
From: John Hasler @ 2005-09-11 17:28 UTC (permalink / raw)
To: pgsql-sql
Greg Sabino Mullane writes:
> Not just old-fashioned, [having only one mother is] the biological law!
I see you aren't up on current research.
--
John Hasler
john@dhh.gt.org
Elmwood, WI USA
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help with 'unique parents' constraint
@ 2005-09-12 18:02 Leif B. Kristensen <leif@solumslekt.org>
parent: Greg Sabino Mullane <greg@turnstep.com>
1 sibling, 2 replies; 7+ messages in thread
From: Leif B. Kristensen @ 2005-09-12 18:02 UTC (permalink / raw)
To: pgsql-sql
On Sunday 11 September 2005 16:04, Greg Sabino Mullane wrote:
> Not just old-fashioned, it's the biological law! (among homo sapiens
> anyway). I'd approach this with a trigger, as you can do complex
> checks and get back nice customized error messages. A sample script
> follows. Hard to tell without seeing your whole schema, but I see no
> need for a relation_id primary key if you already have a unique
> constraint on child_fk and parent_fk, so I made those into the
> primary key for the relations table:
Thank you for an excellent answer. I think I will have to study your
code for a while. But is it such a bad idea to have a separate column
for the primary key here? I see that there are two schools on this,
with diametrically opposed views. For my own part, I feel that it at
least doesn't hurt to have a surrogate key. Secondly, a single key
value is easier to reference from another table than a composite key.
--
Leif Biberg Kristensen
http://solumslekt.org/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help with `unique parents` constraint
@ 2005-09-13 00:38 Greg Sabino Mullane <greg@turnstep.com>
parent: Leif B. Kristensen <leif@solumslekt.org>
1 sibling, 0 replies; 7+ messages in thread
From: Greg Sabino Mullane @ 2005-09-13 00:38 UTC (permalink / raw)
To: pgsql-sql
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
> Thank you for an excellent answer. I think I will have to study your
> code for a while. But is it such a bad idea to have a separate column
> for the primary key here? I see that there are two schools on this,
> with diametrically opposed views. For my own part, I feel that it at
> least doesn't hurt to have a surrogate key. Secondly, a single key
> value is easier to reference from another table than a composite key.
Not "bad", but perhaps slightly inefficient and redundant. It depends on
how your table is actually structured, but if the only way your app
will ever refer to that table is in the context of those 2 foreign
keys, then it makes sense to go ahead and make them a primary key.
If there are other important fields in the table, /and/ if it is referenced
from other tables, then I might add another column. But generally, this
should be the exception and not the rule.
- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200509122031
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----
iD8DBQFDJh99vJuQZxSWSsgRAiRFAJwKiGVsJhcbxIe0nQ3bnxJUZupucACgnUa/
57e9UDfVkv/4AMp2wpqEa3c=
=20d1
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Need help with 'unique parents' constraint
@ 2005-09-14 13:00 Daryl Richter <daryl@brandywine.com>
parent: Leif B. Kristensen <leif@solumslekt.org>
1 sibling, 0 replies; 7+ messages in thread
From: Daryl Richter @ 2005-09-14 13:00 UTC (permalink / raw)
To: pgsql-sql
Leif B. Kristensen wrote:
> On Sunday 11 September 2005 16:04, Greg Sabino Mullane wrote:
>
>
>>Not just old-fashioned, it's the biological law! (among homo sapiens
>>anyway). I'd approach this with a trigger, as you can do complex
>>checks and get back nice customized error messages. A sample script
>>follows. Hard to tell without seeing your whole schema, but I see no
>>need for a relation_id primary key if you already have a unique
>>constraint on child_fk and parent_fk, so I made those into the
>>primary key for the relations table:
>
>
> Thank you for an excellent answer. I think I will have to study your
> code for a while. But is it such a bad idea to have a separate column
> for the primary key here? I see that there are two schools on this,
> with diametrically opposed views. For my own part, I feel that it at
> least doesn't hurt to have a surrogate key. Secondly, a single key
> value is easier to reference from another table than a composite key.
Both are true and as another responder has noted, there are times when
surrogate keys are appropriate.
Be aware, though, that the real danger is data integrity. Should the
alternate key on your composite key get "accidentally" dropped, invalid
data (logical duplicates) can now be inserted.
--
Daryl
"We want great men who, when fortune frowns, will not be discouraged."
-- Colonel Henry Knox, 1776
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2005-09-14 13:00 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-09-11 12:24 Need help with 'unique parents' constraint Leif B. Kristensen <leif@solumslekt.org>
2005-09-11 12:59 ` Leif B. Kristensen <leif@solumslekt.org>
2005-09-11 14:04 ` Greg Sabino Mullane <greg@turnstep.com>
2005-09-11 17:28 ` John Hasler <john@dhh.gt.org>
2005-09-12 18:02 ` Leif B. Kristensen <leif@solumslekt.org>
2005-09-13 00:38 ` Re: Need help with `unique parents` constraint Greg Sabino Mullane <greg@turnstep.com>
2005-09-14 13:00 ` Daryl Richter <daryl@brandywine.com>
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