From: | Jean-Luc Lachance <jllachan(at)nsd(dot)ca> |
---|---|
To: | gss+pg(at)cs(dot)brown(dot)edu |
Cc: | postgres list <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: circular REFERENCES |
Date: | 2002-06-19 18:24:08 |
Message-ID: | 3D10CC48.4E4224E6@nsd.ca |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Work | 2002-06-19 18:44:33 | Optimizing queries |
Previous Message | Tom Jenkins | 2002-06-19 18:11:30 | Re: pl/pgsql function not working |