Re: foreign keys constraints, depending on each other

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