Re: Need help with 'unique parents' constraint - Mailing list pgsql-sql

-----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
NOTNULL 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
REFERENCESpersons (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_idAND p.gender = 1 INTO trace; IF trace IS NOT NULL THEN   IF TG_OP = 'UPDATE' THEN     IF OLD.parent_fk !=
traceTHEN       RAISE EXCEPTION 'Error: Cannot change parent: person % is already assigned as the father', trace;
ENDIF;   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_idAND p.gender = 2 INTO trace; IF trace IS NOT NULL THEN   IF TG_OP = 'UPDATE' THEN     IF OLD.parent_fk !=
traceTHEN       RAISE EXCEPTION 'Error: Cannot change parent: person % is already assigned as the mother', trace;
ENDIF;   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-----