Received: from victoria.host4u.net (victoria.host4u.net [216.71.64.77]) by postgresql.org (8.11.3/8.11.4) with ESMTP id f95CUan99361 for ; Fri, 5 Oct 2001 08:30:36 -0400 (EDT) (envelope-from mcurley@e4media.com) Received: from homerpalooza.e4media.com (pool-162-83-169-143.ny5030.east.verizon.net [162.83.169.143]) by victoria.host4u.net (8.8.5/8.8.5) with ESMTP id HAA00173 for ; Fri, 5 Oct 2001 07:26:46 -0500 Message-Id: <5.0.2.1.0.20011005082745.0273b430@mail.e4media.com> X-Sender: mcurley@mail.e4media.com X-Mailer: QUALCOMM Windows Eudora Version 5.0.2 Date: Fri, 05 Oct 2001 08:30:54 -0400 To: From: Morgan Curley Subject: Re: Problem with n to n relation In-Reply-To: <0110051239400B.30993@janning> Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 8bit X-MIME-Autoconverted: from quoted-printable to 8bit by postgresql.org id f95CUbn99363 X-Archive-Number: 200110/79 X-Sequence-Number: 5034 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 for 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. Morgan At 12:39 PM 10/5/2001 +0200, Janning Vygen wrote: >Hi, > >i create n to n relations like this, right? > >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 canīt >insert any person without adress??? > >thanks in advance >janning > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster ------------- Morgan Curley Partner, e4media mcurley@e4media.com 917 751 8328 http://www.e4media.com -------------------------