From: Greg Sabino Mullane <greg@turnstep.com>
To: pgsql-sql@postgresql.org
Cc: Leif B. Kristensen <leif@solumslekt.org>
Subject: Re: Need help with 'unique parents' constraint
Date: Sun, 11 Sep 2005 14:04:48 -0000
Message-ID: <7a5b3f3e2ddebaeda6d9df07ebca943a@biglumber.com> (raw)
In-Reply-To: <200509111424.04331.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-----
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: greg@turnstep.com, leif@solumslekt.org
Subject: Re: Need help with 'unique parents' constraint
In-Reply-To: <7a5b3f3e2ddebaeda6d9df07ebca943a@biglumber.com>
* 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