Re: foreign keys constraints, depending on each other - Mailing list 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              |
+-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-+