Re: Recursive FOREIGN KEY?

This is possible and works as expected but did require a mild
work-around ...

CREATE TABLE categories (
categoryID integer PRIMARY KEY,
parentID integer,
setID integer REFERENCES categories_sets (setID) ON DELETE CASCADE,
name char(255) NOT NULL
);

INSERT INTO categories VALUES (0,0,0,'DEFAULT');

CREATE INDEX categories_parentID ON categories (categoryID);
CREATE INDEX categories_setID ON categories (setID);

ALTER TABLE categories
ADD CONSTRAINT categories_parentID
FOREIGN KEY (parentID) REFERENCES categories (categoryID) ON DELETE
CASCADE;

I had to insert the initial record and then add the foreign key
restraint after inserting the initial record was created (0 being the
first level of the category structure) - deletes cascade recursively as
expected (sweet).

Thanks!

--Joe

On Sat, 2004-04-03 at 19:46, Joe Stump wrote:
> I have a table that will have a parent/child relationship (specifically
> a recursive collection of categories) and was wondering if I can
> reference a key in the same table ...
>
> CREATE TABLE categories (
> categoryID integer PRIMARY KEY,
> parentID integer REFERENCES categories (categoryID) ON DELETE CASCADE,
>
> ...
> );
>
> Is this supported? Any comments from people out there who have created
> such setups? I'm new to PGSQL and looking hard at converting from MySQL
> so all of these fun features are new to me :)
>
> Thanks!
>
> --Joe
>
> --
> Joe Stump, President
> JCS Solutions
> http://www.jcssolutions.com
> (734) 786 0176
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
--
--
Joe Stump, President
JCS Solutions
p. (734) 786 0176
f. (520) 844 9344
http://www.jcssolutions.com