By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,691 Members | 1,997 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,691 IT Pros & Developers. It's quick & easy.

Tables referencing each other

P: n/a
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 / fe***@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Felix Finch <fe***@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #2

P: n/a
On Thu, Dec 18, 2003 at 23:46:48 -0800,
Felix Finch <fe***@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.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 12 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.