Tables referencing each other

From: Felix Finch <felix(at)crowfix(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Tables referencing each other
Date: 2003-12-19 07:46:48
Message-ID: 16354.44264.715839.870359@crowfix.crowfix.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I am a self taught SQL novice, possibly beneath contempt :-)

Two questions here. One is how to define two tables whose columns
reference each other, the other is whether trying to do this means I
have a bogus design.

CREATE TABLE xyzzy (a INT PRIMARY KEY, b INT REFERENCES plugh);
CREATE TABLE plugh (c INT PRIMARY KEY, d INT REFERENCES xyzzy);

I can't actually do this in 7.4, so I had to create the first table
without the reference.

Is it possible to pre-declare the second table, similar to a C
prototype, so the first definition would not complain about the second
table being unknown?

Assuming there is a way, inserting records should work if DEFERRABLE
is added and the two insertions are done in a transaction. But how
about deleting records, would a transaction allow that? Or should I
add CASCADE?

What I am trying to do is have a linked list, where each item record
also points to the parent list record, and the list record points to
the head and tail item records. Is this something not usually done in
an RDBMS, is this symptomatic of a squirrelly design, is there a
better way to create linked lists, should I just skip having items
point to the parent list, since that can be found from a SELECT
anyway? The list is singly linked, and I create it backwards to avoid
having to DEFER the REFERENCE.

--
... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._.
Felix Finch: scarecrow repairman & rocket surgeon / felix(at)crowfix(dot)com
GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933
I've found a solution to Fermat's Last Theorem but I see I've run out of room o

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Marek Lewczuk 2003-12-19 08:43:21 CASE in where statement. BUG ??
Previous Message Shridhar Daithankar 2003-12-19 06:58:21 Re: PostgreSQL speakers needed for OSCON 2004