Re: circular REFERENCES

Jan Wieck sez:
} 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.

The application is such that nobody can be a member of multiple teams.
Period. It is part of the business logic. The restriction is deliberate. I
know how to make it more flexible but decisions about the application make
that flexibility undesirable.

} Jan
--Greg