From: | Mike Mascari <mascarm(at)mascari(dot)com> |
---|---|
To: | Arjen van der Meijden <acmmailing(at)vulcanus(dot)its(dot)tudelft(dot)nl> |
Cc: | "Tony (Unihost)" <tony(at)unihost(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Tables Referencing themselves As Foreign Keys |
Date: | 2003-12-22 12:00:50 |
Message-ID: | 3FE6DCF2.2060903@mascari.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Arjen van der Meijden | 2003-12-22 12:21:28 | Re: Tables Referencing themselves As Foreign Keys |
Previous Message | Arjen van der Meijden | 2003-12-22 11:36:47 | Re: Tables Referencing themselves As Foreign Keys |