Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C6A042E01E1 for ; Wed, 12 Mar 2008 23:42:54 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 91941-04 for ; Wed, 12 Mar 2008 23:42:51 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from fg-out-1718.google.com (fg-out-1718.google.com [72.14.220.158]) by postgresql.org (Postfix) with ESMTP id A30892E0150 for ; Wed, 12 Mar 2008 23:42:51 -0300 (ADT) Received: by fg-out-1718.google.com with SMTP id 22so2678809fge.23 for ; Wed, 12 Mar 2008 19:42:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=googlemail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=eMcI3Nq8XVMw8VW4zvwlQknDj6VvqgHX8uvWgmirliY=; b=UCiMlvxqWQV2RvjHLAdd/AnSknzF2ACzM2Tqr3EFTgSc5AhYwLUsRaZnr8JhD8sQXrXZmZfCo5E+4vXKMzjtM6cKNL2LgBuvtaapV0Ieamzu4he2JGqsoXIJzRtVaoashb/2adeuYE/riqFNZi8hZZiA8KkeFPd0GbxQz8HE3RQ= DomainKey-Signature: a=rsa-sha1; c=nofws; d=googlemail.com; s=gamma; h=message-id:date:from:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=qOaihLQDVPvT5p7dFRj5IG0+nS6ZA0Y15iVuAqCO0g7TDHtn4H1ogR2nPT8NKj3qnFA+iJYMVOvRyUl7LAn1DoLt5yDfp2L3k1I8sN4sWoxkNfD7FtrJkTaZ9ilQRt7wuzJjtpCpOLmffeslfK+0z9sPnUnliuQFKVjIB8/9lto= Received: by 10.86.62.3 with SMTP id k3mr1301792fga.24.1205376170093; Wed, 12 Mar 2008 19:42:50 -0700 (PDT) Received: by 10.86.4.15 with HTTP; Wed, 12 Mar 2008 19:42:49 -0700 (PDT) Message-ID: Date: Thu, 13 Mar 2008 02:42:49 +0000 From: "Jamie Tufnell" To: pgsql-sql@postgresql.org Subject: Re: Composite UNIQUE across two tables? In-Reply-To: <5d0f60990803111203m18345c5ata6f4d407867ed60d@mail.gmail.com> 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-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200803/64 X-Sequence-Number: 30392 Hi Dirk, On 3/11/08, Dirk Jagdmann wrote: > I vote against duplicating site_group_id in the users table and the > proposed unique constraint with a function. Because all those might > fail, if you ever want to change the relationship between a site and a > site group. Good point! > 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. > One remark about your schema: If you use PostgreSQL, use the "text" > datatype for strings, since you don't limit yourself with the string > length. [snip] For some reason I assumed varchar had an advantage over text, but a quick check of the docs suggests that's not the case. Thanks for this tip! :-) Thank you for your taking the time to write this up, it's very much appreciated. Cheers, J.