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.
JLL
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)
> } > );
> } >
> } > Of course, I can't define them that way. Is there any better way to take
> } > care of this than to leave out the REFERENCES in the first table and add it
> } > with ALTER TABLE ADD CONTRAINT after the second table has been defined?
> }
> } I assume you meant Team on the second create table. Pretty much you will
> } need to use alter table to add one of them. In addition, are you sure
> } you want those to be immediately checked? Generally when you have a
> } recursive structure like that one or both of the constraints is deferred.
>
> Yep, the second table was supposed to be Team. And I do want them both to
> be immediately checked. Users will be added with a NULL team, and will be
> able to join a team.
>
> --Greg
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
