From: | Shaun Thomas <sthomas(at)townnews(dot)com> |
---|---|
To: | <zilch(at)home(dot)se> |
Cc: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: foreign keys constraints, depending on each other |
Date: | 2001-06-11 13:43:39 |
Message-ID: | Pine.LNX.4.30.0106110821300.7965-100000@hamster.lee.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sun, 10 Jun 2001 zilch(at)home(dot)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(at)townnews(dot)com AIM : trifthen |
| Web : hamster.lee.net |
| |
| "Most of our lives are about proving something, either to |
| ourselves or to someone else." |
| -- Anonymous |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+
From | Date | Subject | |
---|---|---|---|
Next Message | Steve Wampler | 2001-06-11 14:26:48 | Postgres and GFS? |
Previous Message | Alex Pilosov | 2001-06-11 13:31:28 | Re: Variables in Postgresql |