Re: Need help with 'unique parents' constraint

-----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(at)turnstep(dot)com
PGP Key: 0x14964AC8 200509110958
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFDJDkzvJuQZxSWSsgRAryTAJ90oT0LWl2ch6c7T7tPsj1/+JpRFwCeOLsV
ceYzuVEHbZPjdCgaMCG65rQ=
=wh38
-----END PGP SIGNATURE-----