Re: Recursive FOREIGN KEY?

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(at)joestump(dot)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