Received: from issv0171.isis.de (issv0171.isis.de [195.158.131.223]) by postgresql.org (8.11.3/8.11.4) with SMTP id f99FvZn06485 for ; Tue, 9 Oct 2001 11:57:35 -0400 (EDT) (envelope-from vygen@planwerk6.de) Received: (qmail 26027 invoked by uid 1010); 9 Oct 2001 15:57:30 -0000 Received: from unknown (HELO janning.planwerk6.local) ([195.158.137.181]) (envelope-sender ) by 0 (qmail-ldap-1.03) with SMTP for ; 9 Oct 2001 15:57:30 -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 f99FtuY05256; Tue, 9 Oct 2001 17:55:56 +0200 From: Janning Vygen Organization: Planwerk 6 /websolutions To: Stephan Szabo Subject: Re: Problem with n to n relation Date: Tue, 9 Oct 2001 17:55:56 +0200 X-Mailer: KMail [version 1.1.99] Content-Type: text/plain; charset="US-ASCII" Cc: pgsql-sql@postgresql.org References: In-Reply-To: MIME-Version: 1.0 Message-Id: <0110091755560U.30615@janning> Content-Transfer-Encoding: 8bit X-Archive-Number: 200110/135 X-Sequence-Number: 5090 Am Dienstag, 9. Oktober 2001 17:38 schrieb Stephan Szabo: > > Yes you are right! It doesnt work. i thought i have tested it... > > > > But i dont understand your solution. Why did you insert foo in > > the person table? Dou want to use it as a foreign key? Maybe you > > just mistyped your alter table statement i guess. you create a > > unique person2address id and references foo to it. So 'foo' will > > always refernce the first address inserted. Right? Ok thats a > > fine solution, but there is more work to do like a trigger when > > deleting this address... > > Yeah, miscopied the statement. And you're right, I'd forgotten > about delete. I think you'd probably be better off faking the > check constraint in a deferred constraint trigger. Thank you very much for your very comprehensive answers. I guess i know enough to solute my problem. Thanks Janning > > Is this the preferable solution?? I am wondering about tutorials > > never explaining stuff like that. Is it too uncommon to have a > > person with at least one address? > > Well, the *best* way (that doesn't work in postgres) is probably > to have a check constraint with a subselect, something like > check exists(select * from person2address where ...) initially > deferred. But we don't support subselect in check directly, and ^^^ ups, your are a developer of postgresql?? thanks for this great database. > its not likely to happen soon (it's a potentially very complicated > constraint). > There are locking issues, but one could probably use a constraint > trigger (a postgres specific thing I think, but...) and have the > trigger do a select * from person2address where... and raise an > exception if no matches are found. The locking issues are due to > the fact that you could run into problems with multiple backends > trying to do stuff to the same rows if you're not careful, although > I think it might work out with for update.