foreign keys constraints, depending on each other

From: zilch(at)home(dot)se
To: pgsql-general(at)postgresql(dot)org
Subject: foreign keys constraints, depending on each other
Date: 2001-06-10 19:31:22
Message-ID: 20010610213122.A29410@loony
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general


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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2001-06-10 20:09:56 Re: inserting, index and no index - speed
Previous Message zilch 2001-06-10 19:19:16 Re: inserting, index and no index - speed