Gregory Seidman wrote:
> 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.
So far so good. That a team can only have one captain, and that the
captainn should also be a member (not enforced in your schema) makes
sense.
But why can nobody be a member of multiple teams? Looks to me like a
restriction that might hurt someday in the future.
Jan
--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck@Yahoo.com #