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@mascari.com