Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 7ADCB2E2E60 for ; Thu, 13 Mar 2008 03:17:04 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 27628-07 for ; Thu, 13 Mar 2008 03:16:58 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from py-out-1112.google.com (py-out-1112.google.com [64.233.166.177]) by postgresql.org (Postfix) with ESMTP id 80B222E2E5F for ; Thu, 13 Mar 2008 03:17:00 -0300 (ADT) Received: by py-out-1112.google.com with SMTP id d37so5952839pye.29 for ; Wed, 12 Mar 2008 23:16:59 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=KLToTndzH4QSfqymTw+gmjp9FJ6k9lkE+8WcLx91/NU=; b=ln3SUAbf5a+fDIqPLO7NaATfD2QYBKHO6PyDf6w02BuJHBRNXF8rX6XRD7FdDdyKU1//YH3d9XLp1qLPLPQHOFzE59tQ0vq0sjrEsEwkt/1AMFfmvCo6gP7qtBoIoX3aIYWhxCtxX4MnjsbGQmCkDQtcPkP4A6/mahzalbWIULM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=LHh8ncHHOvqt1U45B2MViM1vRXBTtqKIgzQBuvQkiJWb3mDl7QZ55f9zhmW2xX45YCILLK+hxEsRVgfZ5so5DxjwAE8OmHZfeaqCixqasIOlN2MJzXY+g2HMnrCfF83zHNArbhVxMqQFl8yE8k5rqbwP5NtwlmFUa3lQlfhpO6Y= Received: by 10.65.240.17 with SMTP id s17mr19550936qbr.53.1205389019079; Wed, 12 Mar 2008 23:16:59 -0700 (PDT) Received: by 10.65.116.5 with HTTP; Wed, 12 Mar 2008 23:16:59 -0700 (PDT) Message-ID: <5d0f60990803122316v30c994cbm3b77a1c2c879d158@mail.gmail.com> Date: Wed, 12 Mar 2008 23:16:59 -0700 From: "Dirk Jagdmann" To: "Jamie Tufnell" Subject: Re: Composite UNIQUE across two tables? Cc: pgsql-sql@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <47D100E0.A3DD.0030.0@indicator.be> <20080307083421.26A9D700009D@mwinf2f21.orange.fr> <47D110C0.A3DD.0030.0@indicator.be> <5d0f60990803111203m18345c5ata6f4d407867ed60d@mail.gmail.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/65 X-Sequence-Number: 30393 > > My advise would be to have two triggers for insert/update on the site > > and users table that check the uniqueness of the username with the > > site_group. A have made some tests with inserts and updates on the > > existing users and sites and these two functions seem to work. > > I think this is the way that I'll go. I'd hoped to somehow express this > solely in the design, if you know what i mean (e.g. without writing > SPs), but it looks like this is the best way to do it. Well I thought about that, but finally came to the conclusion, that standard SQL constraints can not express this inter-table relationships. As I'm not a fan of (artificially) breaking up tables I just wrote those two pl/pgsql functions, because I'd rather have a simple table design and some complicated constraint checking functions than the other way. > Thank you for your taking the time to write this up, it's very much > appreciated. Most people reading this list like to think about/learn from other people's problems. -- ---> Dirk Jagdmann ----> http://cubic.org/~doj -----> http://llg.cubic.org