Re: Tables Referencing themselves As Foreign Keys

Arjen van der Meijden wrote:

> Tony,
>
> That'll work, but you have to mind the first row/toprow you insert.
> Will it have no parent (make the field nullable) or will it be its own
> parent (you'll have to test whether that works, I don't know, foreign
> keys are deferrable, so it should be possible if you specify that).

A more traditional way to have hierarchical relationships in the
relational model is to have two relations (and not use NULLs):

CREATE TABLE categories (
CatID bigint PRIMARY KEY NOT NULL,
CatName text NOT NULL
);

CREATE TABLE category_parents (
CatID bigint UNIQUE NOT NULL REFERENCES categories(CatID),
ParentID bigint NOT NULL REFERENCES categories(CatID)
CHECK (CatID <> ParentID)
);

The top category would be the only tuple in categories that did not
exist in category_parents.

HTH,

Mike Mascari
mascarm(at)mascari(dot)com