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


-----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-----