Thread: Need help with 'unique parents' constraint

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
(genderIN (0,1,2,9)) -- ISO gender codes 
);

CREATE TABLE relations (   relation_id INTEGER PRIMARY KEY,   child_fk INTEGER REFERENCES persons (person_id),
parent_fkINTEGER 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/

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/

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

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

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/

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

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