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

I was getting an error on the parentID reference because there were no
records to reference in the first place. Odd.

--Joe

On Sat, 2004-04-03 at 23:36, Tom Lane wrote:

Joe Stump <joe@joestump.net> writes:

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) -

Why? It worked fine for me without any workaround ...

regression=# CREATE TABLE categories (
regression(# categoryID integer PRIMARY KEY,
regression(# parentID integer REFERENCES categories (categoryID) ON DELETE CASCADE,
regression(# name text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "categories_pkey" for table "categories"
CREATE TABLE
regression=# insert into categories values(0,0,'root');
INSERT 1349044 1
regression=# insert into categories values(1,2,'root');
ERROR: insert or update on table "categories" violates foreign key constraint "$1"
DETAIL: Key (parentid)=(2) is not present in table "categories".
regression=# insert into categories values(1,0,'root');
INSERT 1349046 1
regression=#

regards, tom lane

--
--
Joe Stump, President
JCS Solutions
p. (734) 786 0176
f. (520) 844 9344
http://www.jcssolutions.com

Joe Stump <joe@joestump.net> writes:

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) -

Why? It worked fine for me without any workaround ...

regression=# CREATE TABLE categories (
regression(# categoryID integer PRIMARY KEY,
regression(# parentID integer REFERENCES categories (categoryID) ON DELETE CASCADE,
regression(# name text);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "categories_pkey" for table "categories"
CREATE TABLE
regression=# insert into categories values(0,0,'root');
INSERT 1349044 1
regression=# insert into categories values(1,2,'root');
ERROR: insert or update on table "categories" violates foreign key constraint "$1"
DETAIL: Key (parentid)=(2) is not present in table "categories".
regression=# insert into categories values(1,0,'root');
INSERT 1349046 1
regression=#

regards, tom lane

What you say makes sense - I must have been doing something screwy
because it works fine now.

--Joe

On Sun, 2004-04-04 at 00:44, Tom Lane wrote:

Joe Stump <joe@joestump.net> writes:

I was getting an error on the parentID reference because there were no
records to reference in the first place. Odd.

It should work, because the FK check is an end-of-statement check and
should consider the just-inserted row as available for referencing.
What PG version are you using, and can you show the exact sequence of
operations that produces a failure for you?

regards, tom lane

--
--
Joe Stump, President
JCS Solutions
p. (734) 786 0176
f. (520) 844 9344
http://www.jcssolutions.com

Joe Stump <joe@joestump.net> writes:

I was getting an error on the parentID reference because there were no
records to reference in the first place. Odd.

It should work, because the FK check is an end-of-statement check and
should consider the just-inserted row as available for referencing.
What PG version are you using, and can you show the exact sequence of
operations that produces a failure for you?

regards, tom lane