Re: circular REFERENCES

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