Re: [SQL] Need help with 'unique parents' constraint
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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [SQL] Need help with `unique parents` constraint
-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 [EMAIL PROTECTED] PGP Key: 0x14964AC8 200509122031 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFDJh99vJuQZxSWSsgRAiRFAJwKiGVsJhcbxIe0nQ3bnxJUZupucACgnUa/ 57e9UDfVkv/4AMp2wpqEa3c= =20d1 -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[SQL] 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 (gender IN (0,1,2,9)) -- ISO gender codes ); CREATE TABLE relations ( relation_id INTEGER PRIMARY KEY, child_fk INTEGER REFERENCES persons (person_id), parent_fk INTEGER 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/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [SQL] Need help with 'unique parents' constraint
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/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] 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 [EMAIL PROTECTED]
PGP Key: 0x14964AC8 200509110958
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-BEGIN PGP SIGNATURE-
iD8DBQFDJDkzvJuQZxSWSsgRAryTAJ90oT0LWl2ch6c7T7tPsj1/+JpRFwCeOLsV
ceYzuVEHbZPjdCgaMCG65rQ=
=wh38
-END PGP SIGNATURE-
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Need help with 'unique parents' constraint
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 [EMAIL PROTECTED] Elmwood, WI USA ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
