Re: circular REFERENCES

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.