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