Thread: root of parent-child table with NOT NULL constraint

Hello!

I've created the following table that (I hope) represents groups that
have a relationship to their parent group:

   CREATE TABLE Groups
   (
      Id INTEGER,
      ParentId INTEGER
         CONSTRAINT ParentId_Constraint NOT NULL REFERENCES Groups(Id),
      CONSTRAINT Groups_PK PRIMARY KEY (Id)
   );

The question is: how do I add the first group? The very first group is
the root group, and doesn't have a parent.

My choices seem to be to either:

- create the table without the constraint, add a group that will be the
  parent of all "root" groups, then add the constraint. The problem with
  this solution seems to be that dump and restores will be problematic.

- remove the NOT NULL constraint altogether, but then I won't get the
  kind of referential integerity I want.

So, kind sirs/madams, what am I missing?

Thank you!

Bill Wadley

Bill,

> The question is: how do I add the first group? The very first group
> is
> the root group, and doesn't have a parent.
>
> My choices seem to be to either:
>
> - create the table without the constraint, add a group that will be
> the
>   parent of all "root" groups, then add the constraint. The problem
> with
>   this solution seems to be that dump and restores will be
> problematic.

This is exactly what you do, and pg_dump puts the constraints at the
end of the file to allow this sort of data load.

> So, kind sirs/madams, what am I missing?

Well, you should pick up a copy of Joe Celko's "SQL For Smarties" (2nd
Ed.), which has 20 pages on tree structures that will save you a lot of
headaches.

-Josh