Recursive FOREIGN KEY?

[Joe Stump]

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
[phone]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Joe Stump]

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
[phone]
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

--
--
Joe Stump, President
JCS Solutions
p. [phone]
f. [phone]
http://www.jcssolutions.com
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Tom Lane]

Joe Stump <jo*@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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Joe Stump]

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 <jo*@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. [phone]
f. [phone]
http://www.jcssolutions.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Tom Lane]

Joe Stump <jo*@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

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Joe Stump]

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 <jo*@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. [phone]
f. [phone]
http://www.jcssolutions.com
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend