Обсуждение: Tables referencing each other

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@crowfix.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

Felix Finch <felix@crowfix.com> writes:
> 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?

No.  What you do is create the tables, then ALTER TABLE ADD FOREIGN KEY.

            regards, tom lane

On Thu, Dec 18, 2003 at 23:46:48 -0800,
  Felix Finch <felix@crowfix.com> wrote:
>
> 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.

Normally using a linked list would be a bad idea. Normally when you use
a linked list, you really don't care who is lined to you; you just care
about what the set of values is. If you are storing several different
linked lists in the same table, you can just label rows with a list ID
to indicate which list a row is in. If you have some concept of order, then
you may do something to indicate the order. While their may be times you
would want to do this with a linked list, usually you wouldn't, because
ordering rows based on the links is hard to do efficiently in SQL.