Re: Problem with n to n relation

On Mon, 8 Oct 2001, Janning Vygen wrote:

> Am Freitag, 5. Oktober 2001 14:30 schrieb Morgan Curley:
> > just get rid of the serial_id in person2adress -- there is no
> > reason for it. Make the pk of that table a composite --> person_id,
> > address_id <-- that way you have added some additional integrity to
> > your structure. Only one record can exist ffor a given person at a
> > given address. However any person can have any number of address
> > and any address can have any number of people living at it.
>
> ok fine, i understood it after i figured out what pk means :-)
>
> but how do i reach my goal. It should not be allowed to have a person
> without any address??

Hmm, do you always have at least one known address at the time you're
inserting the person?

I can think of a few somewhat complicated ways. Person getting a column
that references person2adress with initially deferred, the problem
here is that you don't know one of the tables' serial values unless
you're selecting it yourself which would mean you'd have to change
how you were getting your incrementing numbers (getting currval of
some sequence presumably and using that to insert into person2adress).

You could probably also make your own deferred constraint trigger
(although I'm not sure that it's documented since I don't think it was
really meant as a user feature) which does the check at the end of any
transaction in which rows were inserted into person.

> > At 12:39 PM 10/5/2001 +0200, Janning Vygen wrote:
>
> > >create table person (
> > > id serial,
> > > name text
> > >);
> > >
> > >create table address (
> > > id serial,
> > > street text
> > > ...
> > >);
> > >
> > >create table person2adress (
> > > id serial,
> > > person_id integer not null references person(id),
> > > address_id integer not null references address(id),
> > >);
> > >
> > >than i can select all adresses from one person with id =1 with
> > >select street
> > >from address
> > >where id =
> > > (
> > > select adress_id
> > > from person2adress
> > > where person_id = 1
> > > );
> > >
> > >ok so far so good. but you can still insert persons without any
> > >adress. so its a 0..n relation. But how van i achieve that you
> > > cant insert any person without adress???