Re: circular REFERENCES

I know exactly what you what to do but...
Try to do a dump and restore...

Gregory Seidman wrote:
>
> Jean-Luc Lachance sez:
> } Gregory Seidman wrote:
> } > Stephan Szabo sez:
> } > } On Tue, 18 Jun 2002, Gregory Seidman wrote:
> } > } > I would like to define two tables:
> } > } >
> } > } > CREATE TABLE Person (
> } > } > id SERIAL not null,
> } > } > -- ...
> } > } > team_membership integer default null REFERENCES Team(id),
> } > } > primary key (id)
> } > } > );
> } > } > CREATE TABLE Person (
> } > } > id SERIAL not null,
> } > } > -- ...
> } > } > captain integer not null REFERENCES Person(id),
> } > } > primary key (uid)
> } > } > );
> [...]
> } That (circular ref) is bad idea.
> } Let's see:
> }
> } You want to create a new team.
> } You must specify the captain so you must add a person.
> } You want to create that person but the team must exist.
> } [loop for ever]
> }
> } You should remove captain from Teams and add isCaptain to Persons.
> } Or better yet:
> }
> } create table TeamCaptains(
> } person references Persons(id),
> } team references Teams(id)
> } );
> }
> } You can solve the one captain per team with a rule.
>
> You misunderstand what's going on. A person need not be on a team. A person
> is always created with a NULL team. A person can then join a team, in which
> case the team attribute gets a value. A person could, instead, create a
> team with himself as captain (and he would also join the newly created
> team). The circular foreign key reference *is* semantically meaningful. If
> both the captain and team_membership attributes were declared not null,
> then there would be the chicken and egg problem you describe.
>
> Furthermore, if I did it your way I wouldn't need a rule to make sure each
> team has only one captain. I just need to declare the team attribute as
> UNIQUE.
>
> In any case, I solved it simply by using ALTER TABLE ADD CONSTRAINT after
> defining the first without the REFERENCES and the second table as is. All
> is well. The thread is closed.
>
> } JLL
> --Greg
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html