Thread: foreign keys constraints, depending on each other
I was just creating this little database for demonstrating the use of
foreign keys constraints.
I was about the create 3 tables, namely mother, father and child. Mother has
a foreign key pointing at father ( id ), and father has a foreign key
pointing at mother ( id ). Child has one pointer to mother ( id ) and one
pointer to father ( id ). How can I prevent the error message from occurring?
Ofcourse I see the problem here... just by taking away the references
keyword from the mother table takes away the problem completely.
---
DROP SEQUENCE mother_id_seq;
DROP SEQUENCE father_id_seq;
DROP SEQUENCE child_id_seq;
DROP TABLE mother;
DROP TABLE father;
DROP TABLE child;
CREATE TABLE mother (
id SERIAL,
fatherID INT4 NOT NULL REFERENCES father ( id ) ON DELETE CASCADE,
name TEXT,
UNIQUE ( fatherID )
);
CREATE TABLE father (
id SERIAL,
motherID INT4 NOT NULL REFERENCES mother ( id ) ON DELETE CASCADE,
name TEXT,
UNIQUE ( motherID )
);
CREATE TABLE child (
id SERIAL,
motherID INT4 NOT NULL REFERENCES mother ( id ) ON DELETE CASCADE,
fatherID INT4 NOT NULL REFERENCES father ( id ) ON DELETE CASCADE,
name TEXT
);
---
Thanks
Daniel Akerud
On Sun, 10 Jun 2001 zilch@home.se wrote: > > I was just creating this little database for demonstrating the use of > foreign keys constraints. > > I was about the create 3 tables, namely mother, father and child. Mother has > a foreign key pointing at father ( id ), and father has a foreign key > pointing at mother ( id ). Child has one pointer to mother ( id ) and one > pointer to father ( id ). How can I prevent the error message from occurring? You don't put the constraint at table creation time. The table referenced by the references has to exist. Use ALTER TABLE to add the constraint after creating table father.
> > I was just creating this little database for demonstrating the use of > > foreign keys constraints. > > > > I was about the create 3 tables, namely mother, father and child. Mother has > > a foreign key pointing at father ( id ), and father has a foreign key > > pointing at mother ( id ). Child has one pointer to mother ( id ) and one > > pointer to father ( id ). How can I prevent the error message from occurring? > > You don't put the constraint at table creation time. The table referenced > by the references has to exist. Use ALTER TABLE to add the constraint > after creating table father. > I tried: BEGIN; SET CONSTRAINTS ALL DEFERRED; INSERT INTO mother (fatherID, name) VALUES ( 1, 'mamma' ) ; INSERT INTO father (motherID, name) VALUES ( 1, 'pappa' ) ; INSERT INTO child (motherID, fatherID, name) VALUES (1, 1, 'barn 1') ; INSERT INTO child (motherID, fatherID, name) VALUES (1, 1, 'barn 2') ; COMMIT; ...which did not work. Still it complains about key referenced from mother not found in father. --- Daniel Akerud
On Sun, 10 Jun 2001 zilch@home.se wrote: > > > > I was just creating this little database for demonstrating the use of > > > foreign keys constraints. > > > > > > I was about the create 3 tables, namely mother, father and child. Mother has > > > a foreign key pointing at father ( id ), and father has a foreign key > > > pointing at mother ( id ). Child has one pointer to mother ( id ) and one > > > pointer to father ( id ). How can I prevent the error message from occurring? > > > > You don't put the constraint at table creation time. The table referenced > > by the references has to exist. Use ALTER TABLE to add the constraint > > after creating table father. > > > > I tried: > > BEGIN; > SET CONSTRAINTS ALL DEFERRED; > INSERT INTO mother (fatherID, name) VALUES ( 1, 'mamma' ) ; > INSERT INTO father (motherID, name) VALUES ( 1, 'pappa' ) ; > INSERT INTO child (motherID, fatherID, name) VALUES (1, 1, 'barn 1') ; > INSERT INTO child (motherID, fatherID, name) VALUES (1, 1, 'barn 2') ; > COMMIT; > > ...which did not work. Still it complains about key referenced from mother not > found in father. Ah, that's because you didn't define the constraints DEFERRABLE. SET CONSTRAINTS ALL DEFERRED only changes the state of deferrable constraints. If you don't specify a time, it's INITIALLY IMMEDIATE. If it's initially immediate, it's NOT DEFERRABLE unless DEFERRABLE is explicitly given.
On Sun, 10 Jun 2001 zilch@home.se wrote: > I was about the create 3 tables, namely mother, father and child. Mother has > a foreign key pointing at father ( id ), and father has a foreign key > pointing at mother ( id ). Child has one pointer to mother ( id ) and one > pointer to father ( id ). How can I prevent the error message from occurring? Personnally, I tend to avoid those circular references in any computing field. I would remove the father and mother references, and add a is_married relation; as a table, with a UNIQUE(father_id), UNIQUE(mother_id) constraint (a person can be only married once). I would keep the direct references from child. It might a bit diminush the performance, but circular references are a pain to handle. Alternatively, keep only the mother -> father reference, and determine the wife of father through query like: SELECT m.id FROM mother m WHERE m.father_id = ? This can be quite efficient if the mother was looked up previously anyway.
On Mon, 11 Jun 2001, Mario Weilguni wrote: > > relation; as a table, with a UNIQUE(father_id), UNIQUE(mother_id) > > constraint (a person can be only married once). > > Is not true, at least not in some arabic countries. in that case my model is even better since it *allows* for that case (by removing the UNIQUE constraints), where the REFERENCES model has an issue.
> > > relation; as a table, with a UNIQUE(father_id), UNIQUE(mother_id) > > > constraint (a person can be only married once). > > > > Is not true, at least not in some arabic countries. > > in that case my model is even better since it *allows* for that case (by > removing the UNIQUE constraints), where the REFERENCES model has an issue. ( =) ) Thanks for the tip! I think i think i'll use that one... Daniel Akerud
On Sun, 10 Jun 2001 zilch@home.se wrote:
> Ofcourse I see the problem here... just by taking away the references
> keyword from the mother table takes away the problem completely.
Your problem isn't just one of references. Your inherant table design is
flawed. If you go by a strict relationship pairing, fatherid and motherid
should only be paired once. You can get less tables if you use this
structure, similar to a geneology system:
---
DROP SEQUENCE seq_personid;
DROP SEQUENCE seq_relationid;
DROP TABLE person;
DROP TABLE relation;
CREATE TABLE person
(
personid INTEGER NOT NULL DEFAULT NEXTVAL('seq_personid'),
first VARCHAR(50) NOT NULL,
middle VARCHAR(50),
last VARCHAR(50) NOT NULL,
birthdate DATETIME NOT NULL,
sex CHAR(1) NOT NULL,
CONSTRAINT pk_person PRIMARY KEY (personid)
);
CREATE TABLE relation
(
relationid INTEGER NOT NULL DEFAULT NEXTVAL('seq_relationid'),
firstid INTEGER NOT NULL,
secondid INTEGER NOT NULL,
reldate DATETIME NOT NULL,
reldesc VARHCAR(50) NOT NULL,
CONSTRAINT pk_relation PRIMARY KEY (relationid)
);
ALTER TABLE relation ADD CONSTRAINT fk_person_personid_1 FOREIGN KEY
(firstid) REFERENCES person (personid) ON DELETE CASCADE;
ALTER TABLE relation ADD CONSTRAINT fk_person_personid_2 FOREIGN KEY
(secondid) REFERENCES person (personid) ON DELETE CASCADE;
CREATE SEQUENCE seq_personid
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
CREATE SEQUENCE seq_relationid
INCREMENT 1
MINVALUE 1
MAXVALUE 2147483647
START 1
CACHE 1;
---
Theoretically, this structure lets you add and subtract parts of
your table structure at will without damaging anything. Don't like
your constraints for a table load? Remove them temporarily. You
might want to also think about making another table to hold the
reldesc and key it into relation, since you most likely have a
constrained subset of defined relationships: Married, Son, Daughter,
etc.
Why not drop constraint? Last I checked in postgres 7.0, that is
not an allowed operation. I still suggest using this syntax though,
since the ability may be added in the future.
--
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
| Shaun M. Thomas INN Database Programmer |
| Phone: (309) 743-0812 Fax : (309) 743-0830 |
| Email: sthomas@townnews.com AIM : trifthen |
| Web : hamster.lee.net |
| |
| "Most of our lives are about proving something, either to |
| ourselves or to someone else." |
| -- Anonymous |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
Am Montag, 11. Juni 2001 10:25 schrieb Marc SCHAEFER: > I would remove the father and mother references, and add a > is_married > relation; as a table, with a UNIQUE(father_id), UNIQUE(mother_id) > constraint (a person can be only married once). Is not true, at least not in some arabic countries. -- =================================================== Mario Weilguni KPNQwest Austria GmbH Senior Engineer Web Solutions Nikolaiplatz 4 tel: +43-316-813824 8020 graz, austria fax: +43-316-813824-26 http://www.kpnqwest.at e-mail: mario.weilguni@kpnqwest.com ===================================================