Thread: circular REFERENCES

circular REFERENCES

From

Gregory Seidman

Date:

18 June 2002, 20:23:59

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?

--Greg

Re: circular REFERENCES

From

Stephan Szabo

Date:

18 June 2002, 21:34:33

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.

Re: circular REFERENCES

From

Gregory Seidman

Date:

18 June 2002, 21:42:14

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

Re: circular REFERENCES

From

Jean-Luc Lachance

Date:

19 June 2002, 18:02:17

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

Re: circular REFERENCES

From

Gregory Seidman

Date:

20 June 2002, 00:06:13

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

Re: circular REFERENCES

From

Jan Wieck

Date:

20 June 2002, 11:17:24

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 #

Re: circular REFERENCES

From

Jean-Luc Lachance

Date:

20 June 2002, 14:29:00

I know exactly what you what to do but...
Try to do a dump and restore...

Gregory Seidman wrote:
>
> 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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

Re: circular REFERENCES

From

Jan Wieck

Date:

20 June 2002, 16:21:15

Jean-Luc Lachance wrote:
>
> I know exactly what you what to do but...
> Try to do a dump and restore...

If you think dump and restore would fail on circular references you're
wrong. They get restored and it works perfectly good, even if you do a
data-only dump, because pg_dump then emits special commands disabling
triggers before loading the data :-)

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: circular REFERENCES

From

Gregory Seidman

Date:

20 June 2002, 20:05:17

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