Received: from issv0170.isis.de (issv0170.isis.de [195.158.131.222]) by postgresql.org (8.11.3/8.11.4) with SMTP id f98HIln13655 for ; Mon, 8 Oct 2001 13:18:47 -0400 (EDT) (envelope-from vygen@planwerk6.de) Received: (qmail 21134 invoked by uid 1010); 8 Oct 2001 17:18:45 -0000 Received: from unknown (HELO janning.planwerk6.local) ([195.158.137.2]) (envelope-sender ) by 0 (qmail-ldap-1.03) with SMTP for ; 8 Oct 2001 17:18:45 -0000 Received: from janning (localhost [127.0.0.1]) by janning.planwerk6.local (8.11.2/8.11.2/SuSE Linux 8.11.1-0.5) with SMTP id f98HH5Y23897; Mon, 8 Oct 2001 19:17:05 +0200 From: Janning Vygen Organization: Planwerk 6 /websolutions To: Stephan Szabo Subject: Re: Problem with n to n relation Date: Mon, 8 Oct 2001 19:17:05 +0200 X-Mailer: KMail [version 1.1.99] Content-Type: text/plain; charset="X-UNKNOWN" Cc: pgsql-sql@postgresql.org References: In-Reply-To: MIME-Version: 1.0 Message-Id: <0110081917050A.21592@janning> Content-Transfer-Encoding: 8bit X-Archive-Number: 200110/123 X-Sequence-Number: 5078 Am Montag, 8. Oktober 2001 18:09 schrieb Stephan Szabo: > On Mon, 8 Oct 2001, Janning Vygen wrote: > > > 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). yeah, thats a way which works. dont know if its cool to do it like this, but you cant insert a person without any address. so you are forced to use a transaction. create table person ( id serial, name text ); create table address ( id serial, street text NOT NULL ); create table person2address ( id int4, address_id int4 NOT NULL REFERENCES address (id), person_id int4 NOT NULL REFERENCES person (id) ); ALTER TABLE person ADD CONSTRAINT person_has_adress FOREIGN KEY (id) REFERENCES person2address(id) DEFERRABLE INITIALLY DEFERRED; begin; insert into person (name) values ('janning'); insert into address (street) values ('Sesamestreet'); insert into person2address values(1,1,1); commit; > 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), > > > >); > > > > -- Planwerk 6 /websolutions Herzogstraße 86 40215 Düsseldorf fon 0211-6015919 fax 0211-6015917 http://www.planwerk6.de