Re: foreign keys constraints, depending on each other
От | Shaun Thomas |
---|---|
Тема | Re: foreign keys constraints, depending on each other |
Дата | |
Msg-id | Pine.LNX.4.30.0106110821300.7965-100000@hamster.lee.net обсуждение исходный текст |
Ответ на | foreign keys constraints, depending on each other (zilch@home.se) |
Список | pgsql-general |
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 | +-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
В списке pgsql-general по дате отправления: