Re: Tables Referencing themselves As Foreign Keys

This is a fine approach.  The FK will work fine.  You'll probably want CatID
to be NOT NULL and CatParent to allow nulls.  Having a Null parent
indicating root is easier for traversals.

Common other features to add include:
     a "path" column that is maintaned by insert/update triggers.  Quite
easy to do and very helpful.
 Once you have that you can do a simple test for circularity also on
insert/update, like:
         IF "path" ~ '(^|\\.)' || "CatID"::text || '(\\.|$)'   THEN
             RAISE EXCEPTION ''circular hierarchy detected...'';
         END IF;
There's also a short-cut way to do this since you use Serial for the CatIDs.
Just do a CHECK (CatParent < CatID) -- of course it makes an assumption
about the CatIDs really come in serially...

== Ezra Epstein


""Tony (Unihost)"" <tony@unihost.net> wrote in message
news:3FE6CE27.5080102@unihost.net...
> Hi,
>
> I'm still new to this so if I'm sounding dumb or my premise is flawed
> please forgive me.  I have a DB design which contains a table which has
> categories, each category has a parent category, and is recursed until
> the top category is reached, in order to create breadcrumbs.  Is there
> any problem with using foreign keys to reference the same table?  So a
> when category is added the CatParent MUST be present as a CatID
>
> CatID - Serial
> CatParent - int4 - References CatID
> CatName - Text
>
> Am I likeley to come unstuck with this?
>
> Cheers
>
> T.
>
>
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>