By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,663 Members | 1,271 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,663 IT Pros & Developers. It's quick & easy.

Recursive FOREIGN KEY?

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
5 Replies

P: n/a
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
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

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

Nov 23 '05 #2

P: n/a
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

Nov 23 '05 #3

P: n/a
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. (734) 786 0176
f. (520) 844 9344
http://www.jcssolutions.com
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #4

P: n/a
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

Nov 23 '05 #5

P: n/a
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. (734) 786 0176
f. (520) 844 9344
http://www.jcssolutions.com
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 23 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.