Обсуждение: Recursive FOREIGN KEY?
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
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
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
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 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
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