X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 667C911F66FC for ; Wed, 12 Apr 2006 22:26:20 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: 0.131 X-Spam-Level: X-Spam-Status: No, score=0.131 required=5 tests=[AWL=0.131] Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id XbeHtDp+f2i8 for ; Wed, 12 Apr 2006 22:26:15 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from megazone.bigpanda.com (megazone.bigpanda.com [64.147.171.210]) by postgresql.org (Postfix) with ESMTP id AD4AE11F6009 for ; Wed, 12 Apr 2006 22:26:17 -0300 (ADT) Received: by megazone.bigpanda.com (Postfix, from userid 1001) id 9CA68350CC; Wed, 12 Apr 2006 18:26:18 -0700 (PDT) Received: from localhost (localhost [127.0.0.1]) by megazone.bigpanda.com (Postfix) with ESMTP id 9ABDC350CA; Wed, 12 Apr 2006 18:26:18 -0700 (PDT) Date: Wed, 12 Apr 2006 18:26:18 -0700 (PDT) From: Stephan Szabo To: George Young Cc: pgsql-sql@postgresql.org Subject: Re: trigger to enforce FK with nulls? In-Reply-To: <20060412211118.05411612.gry@ll.mit.edu> Message-ID: <20060412182501.X57788@megazone.bigpanda.com> References: <20060412211118.05411612.gry@ll.mit.edu> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Archive-Number: 200604/87 X-Sequence-Number: 24896 On Wed, 12 Apr 2006, George Young wrote: > [PostgreSQL 8.1.3 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.0.1] > > I'm starting to use lots of foreign key constraints to keep my > data clean. In one case, however, I need to allow null values > for the key. E.g.: > > create table opset_steps(opset text, step text, step_num int); > create table steps(run text, step text, opset text, user text, step_num int); > > The constraint on steps should be: > steps.(opset,step) must be found in opset_steps.(opset,step) UNLESS steps.opset is null. The default foreign key case should not error if either steps.opset or steps.step is null. If you're seeing something else, can you give a complete test case?