Re: circular REFERENCES

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