X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id C68B111F6225 for ; Sat, 29 Apr 2006 17:27:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 00285-05 for ; Sat, 29 Apr 2006 17:27:07 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from wolff.to (wolff.to [66.93.197.194]) by postgresql.org (Postfix) with SMTP id 9B7FA11F6210 for ; Sat, 29 Apr 2006 17:27:05 -0300 (ADT) Received: (qmail 16204 invoked by uid 500); 29 Apr 2006 20:27:37 -0000 Date: Sat, 29 Apr 2006 15:27:37 -0500 From: Bruno Wolff III To: Jeff Frost Cc: Phillip Tornroth , pgsql-sql@postgresql.org Subject: Re: Multi-Column Constraints and Null Values Message-ID: <20060429202737.GB14915@wolff.to> Mail-Followup-To: Bruno Wolff III , Jeff Frost , Phillip Tornroth , pgsql-sql@postgresql.org References: <303D0B49-F8EC-491C-8865-B7B819120C13@intellidot.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.4.2.1i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200604/218 X-Sequence-Number: 25027 On Sat, Apr 29, 2006 at 13:14:36 -0700, Jeff Frost wrote: > On Thu, 27 Apr 2006, Phillip Tornroth wrote: > > >I have a problem in a number of my tables. I'd like to add uniqueness > >constraints across multiple columns, but some of the columns are nullable. > >This gives me trouble since when I create a constraint on columns A and > >B.. I'd like the constraint to be enforced such that you couldn't insert > >values ("value for A", null) twice. I understand why the constraints work > >this way, but I'm wondering if anyone knows of a workaround. > > > > Phill, > > You likely want a multicolumn unique index created like so: > > CREATE UNIQUE INDEX foo_A_B_unique_idx ON foo (A,B); > > See the docs here: > http://www.postgresql.org/docs/current/interactive/indexes-unique.html Creating a multicolumn key is a simpler way of doing the same thing. However, either way of doing the above doesn't completely solve his problem. To block multiple entries where one of the columns is null he needs to add extra checks. One way to do this is to add a partial index for each column with the condition that the other column IS NULL. This will cover everything but the case where both columns are null. If this case isn't allowed, then a row constraint can be used to block it. If it is allowed, the only thing that comes to mind is making an SQL function that counts the number of rows where both columns are null and calling that function in a row constraint and checking that the result is <= 1.